有一张表为emp_salary用于记录员工薪水，salary1—salary12分别存放员工12个月每月的工资，结构如下：empno name Salary1 Salary2 Salary3 Salary4 Salary5 Salary6 Salary7 Salary8 Salary9 Salary10 Salary11 Salary12
但希望用以下表结构存放所有员工的薪水：empno Name Month salary
请用一条sql语句实现以上要求:
但希望用以下表结构存放所有员工的薪水：empno Name Month salary
请用一条sql语句实现以上要求:
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> 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 100
A002 RAIN 1 1200
A001 SUN 2 200
A002 RAIN 2 1100
A001 SUN 3 300
A002 RAIN 3 1000
A001 SUN 4 400
A002 RAIN 4 900
A001 SUN 5 500
A002 RAIN 5 800
A001 SUN 6 600
A002 RAIN 6 700
A001 SUN 7 700
A002 RAIN 7 600
A001 SUN 8 800
A002 RAIN 8 500
A001 SUN 9 900
A002 RAIN 9 400
A001 SUN 10 1000
A002 RAIN 10 300
EMPNO NAME MONTH SALARY
----- ---- ---------- ----------
A001 SUN 11 1100
A002 RAIN 11 200
A001 SUN 12 1200
A002 RAIN 12 100
24 rows selected
SQL>
方法2： UNPIVOT,适用范围：11g
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 * FROM TEST
8 UNPIVOT(
9 SALARY FOR MONTH IN(Salary1 as 1 ,
10 Salary2 as 2 ,
11 Salary3 as 3 ,
12 Salary4 as 4 ,
13 Salary5 as 5 ,
14 Salary6 as 6 ,
15 Salary7 as 7 ,
16 Salary8 as 8 ,
17 Salary9 as 9 ,
18 Salary10 as 10,
19 Salary11 as 11,
20 Salary12 as 12)
21 )
22 ;
EMPNO NAME MONTH SALARY
----- ---- ---------- ----------
A001 SUN 1 100
A001 SUN 2 200
A001 SUN 3 300
A001 SUN 4 400
A001 SUN 5 500
A001 SUN 6 600
A001 SUN 7 700
A001 SUN 8 800
A001 SUN 9 900
A001 SUN 10 1000
A001 SUN 11 1100
A001 SUN 12 1200
A002 RAIN 1 1200
A002 RAIN 2 1100
A002 RAIN 3 1000
A002 RAIN 4 900
A002 RAIN 5 800
A002 RAIN 6 700
A002 RAIN 7 600
A002 RAIN 8 500
EMPNO NAME MONTH SALARY
----- ---- ---------- ----------
A002 RAIN 9 400
A002 RAIN 10 300
A002 RAIN 11 200
A002 RAIN 12 100
24 rows selected