网上的一个SQL查询有关问题，很多人应该知道，请不吝赐教：

2012-05-23

网上的一个SQL查询问题，很多人应该知道，请不吝赐教：？有一张表为emp_salary用于记录员工薪水，salary1—salar

网上的一个SQL查询问题，很多人应该知道，请不吝赐教：？
有一张表为emp_salary用于记录员工薪水，salary1—salary12分别存放员工12个月每月的工资，结构如下：

empnonameSalary1Salary2Salary3Salary4Salary5Salary6Salary7Salary8Salary9Salary10Salary11Salary12

但希望用以下表结构存放所有员工的薪水：

empnoNameMonthsalary

请用一条sql语句实现以上要求:

[解决办法]
比较简单的题了，自己东东脑子啊

SQL code

 
  
 insert into newSalaryTable 
 (empno, name, salary, month) 
 select empno, name, salary, month 
   from (select empno, name, Salary1 salary, '1月' month 
       from salaryTable 
     union all 
     select empno, name, Salary2 salary, '2月' month 
       from salaryTable 
     union all 
     select empno, name, Salary3 salary, '3月' month 
       from salaryTable 
     union all 
     select empno, name, Salary4 salary, '4月' month 
       from salaryTable 
     union all 
     select empno, name, Salary5 salary, '5月' month 
       from salaryTable 
     union all 
     select empno, name, Salary6 salary, '6月' month 
       from salaryTable 
     union all 
     select empno, name, Salary7 salary, '7月' month 
       from salaryTable 
     union all 
     select empno, name, Salary8 salary, '8月' month 
       from salaryTable 
     union all 
     select empno, name, Salary9 salary, '9月' month 
       from salaryTable 
     union all 
     select empno, name, Salary10 salary, '10月' month 
       from salaryTable 
     union all 
     select empno, name, Salary11 salary, '11月' month 
       from salaryTable 
     union all 
     select empno, name, Salary12 salary, '12月' month from salaryTable);

[解决办法]
行转列问题：
方法1：union all ,适用范围：8i,9i,10g及以后版本

SQL code

SQL> WITH TEST AS(  2  SELECT 'A001' AS empno,'SUN' AS name ,100 Salary1,200 Salary2,300 Salary3,400 Salary4  3  ,500 Salary5,600 Salary6,700 Salary7,800 Salary8,900 Salary9,1000 Salary10,1100 Salary11,1200 Salary12  4  FROM DUAL UNION  5  SELECT 'A002','RAIN',1200,1100,1000,900,800,700,600,500,400,300,200,100 FROM DUAL  6  )  7  SELECT EMPNO,NAME,1 AS MONTH,SALARY1 AS SALARY FROM TEST  8  UNION ALL  9  SELECT EMPNO,NAME,2 AS MONTH,SALARY2 AS SALARY FROM TEST 10  UNION ALL 11  SELECT EMPNO,NAME,3 AS MONTH,SALARY3 AS SALARY FROM TEST 12  UNION ALL 13  SELECT EMPNO,NAME,4 AS MONTH,SALARY4 AS SALARY FROM TEST 14  UNION ALL 15  SELECT EMPNO,NAME,5 AS MONTH,SALARY5 AS SALARY FROM TEST 16  UNION ALL 17  SELECT EMPNO,NAME,6 AS MONTH,SALARY6 AS SALARY FROM TEST 18  UNION ALL 19  SELECT EMPNO,NAME,7 AS MONTH,SALARY7 AS SALARY FROM TEST 20  UNION ALL 21  SELECT EMPNO,NAME,8 AS MONTH,SALARY8 AS SALARY FROM TEST 22  UNION ALL 23  SELECT EMPNO,NAME,9 AS MONTH,SALARY9 AS SALARY FROM TEST 24  UNION ALL 25  SELECT EMPNO,NAME,10 AS MONTH,SALARY10 AS SALARY FROM TEST 26  UNION ALL 27  SELECT EMPNO,NAME,11 AS MONTH,SALARY11 AS SALARY FROM TEST 28  UNION ALL 29  SELECT EMPNO,NAME,12 AS MONTH,SALARY12 AS SALARY FROM TEST; EMPNO NAME      MONTH     SALARY----- ---- ---------- ----------A001  SUN           1        100A002  RAIN          1       1200A001  SUN           2        200A002  RAIN          2       1100A001  SUN           3        300A002  RAIN          3       1000A001  SUN           4        400A002  RAIN          4        900A001  SUN           5        500A002  RAIN          5        800A001  SUN           6        600A002  RAIN          6        700A001  SUN           7        700A002  RAIN          7        600A001  SUN           8        800A002  RAIN          8        500A001  SUN           9        900A002  RAIN          9        400A001  SUN          10       1000A002  RAIN         10        300 EMPNO NAME      MONTH     SALARY----- ---- ---------- ----------A001  SUN          11       1100A002  RAIN         11        200A001  SUN          12       1200A002  RAIN         12        100 24 rows selected SQL>

热点排行

oracle

网上的一个SQL查询有关问题，很多人应该知道，请不吝赐教：