日期:2014-05-17  浏览次数:20806 次

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

empno name Salary1 Salary2 Salary3 Salary4 Salary5 Salary6 Salary7 Salary8 Salary9 Salary10 Salary11 Salary12
   
   
 
但希望用以下表结构存放所有员工的薪水:

empno Name Month salary
   
   

请用一条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