"PIVOT"关系运算符 一般用来解决‘行转列’的需求,比如常用于按周,月汇总数据。
用一个按月份汇总薪资的列子来看
需求:
有一张有员工,时间,薪资的数据表,需要按月份汇总员工当月每天的薪资到一行展示,结果图如下
月份 | 员工号 | 1号工资 | 2号工资 | ... | 30号工资 | 31号工资 |
数据结构:
create table ABCS
(
UPDATE_DATE DATE not null,
EMPOLYEE_NAME NVARCHAR2(80),
SALARY NUMBER
)
数据如下分别是6月1号和6月2号的工资
时间 | 员工 | 工资 | 时间
| 员工 | 工资 |
20200701 | 张三 | 12 | 20200601 | 张三 | 12 |
20200701 | 李四 | 10 | 20200601 | 李四 | 10 |
20200702 | 张三 | 11 | 20200602 | 张三 | 11 |
20200702 | 李四 | 20 | 20200602 | 李四 | 20 |
20200701 | 张三 | 20 | 20200601 | 张三 | 20 |
INSERT INTO ABCS VALUES (2020/7/1, 张三, 12);
INSERT INTO ABCS VALUES (2020/7/2, 张三, 11);
INSERT INTO ABCS VALUES (2020/7/2, 李四, 20);
INSERT INTO ABCS VALUES (2020/7/1, 张三, 20);
INSERT INTO ABCS VALUES (2020/7/1, 张三, 20);
INSERT INTO ABCS VALUES (2020/6/1, 张三, 12);
INSERT INTO ABCS VALUES (2020/6/1, 李四, 10);
INSERT INTO ABCS VALUES (2020/6/2, 张三, 11);
INSERT INTO ABCS VALUES (2020/6/2, 李四, 20);
INSERT INTO ABCS VALUES (2020/6/1, 张三, 20);
>>>>>>>>不用PIVOT函数:根据每天先拆分薪资,再汇总<<<<<<<<
SQL如下
SELECT UI.NAME AS 姓名,
UI.MONTH AS 月份,
SUM(UI.ONE) AS 一号薪资,
SUM(UI.TWO) AS 二号薪资
FROM (
SELECT DISTINCT AB.EMPOLYEE_NAME AS NAME,
TO_CHAR(AB.UPDATE_DATE, 'MM') AS MONTH,
DECODE(TO_CHAR(AB.UPDATE_DATE, 'dd'),
1,
nvl(AB.SALARY, 0)) AS ONE,
DECODE(TO_CHAR(AB.UPDATE_DATE, 'dd'),
2,
nvl(AB.SALARY, 0)) AS TWO
FROM ABCS AB
GROUP BY AB.EMPOLYEE_NAME, AB.UPDATE_DATE, AB.SALARY
) UI
group by UI.NAME, UI.MONTH
运行结果:
>>>>>>>>用pivot方式后<<<<<<<<
SQL如下:
SELECT UI.NAME AS 姓名,
UI.MONTH AS 月份,
NVL(UI.一号, 0) AS 一号薪资,
NVL(UI.二号, 0) AS 二号薪资
FROM (SELECT AB.EMPOLYEE_NAME AS NAME,
TO_CHAR(AB.UPDATE_DATE, 'MM') AS MONTH,
TO_CHAR(AB.UPDATE_DATE, 'dd') DAY,
AB.SALARY
FROM ABCS AB) T PIVOT(SUM(T.SALARY) FOR DAY IN('01' AS 一号,
'02' AS 二号)) UI
运行结果:
参考:
http://technet.microsoft.com/zh-cn/library/ms177410(v=sql.105).aspx
总结:
1,查除基础数据表格,确定以那一列汇总,那一列为根据
2, 添加PIVOT :SUM聚合函数中放入汇总字段(SALARY),FOR 后面 添加 根据字段(DAY),其中SUM函数可以是其他计算函数