pivot:pivot的用法 2024-04-24 18:54:36 0 0 "PIVOT"关系运算符 一般用来解决‘行转列’的需求,比如常用于按周,月汇总数据。 用一个按月份汇总薪资的列子来看 需求: 有一张有员工,时间,薪资的数据表,需要按月份汇总员工当月每天的薪资到一行展示,结果图如下月份员工号1号工资2号工资...30号工资31号工资 数据结构: create table ABCS ( UPDATE_DATE DATE not null, EMPOLYEE_NAME NVARCHAR2(80), SALARY NUMBER ) 数据如下分别是6月1号和6月2号的工资时间员工工资 时间 员工工资20200701张三1220200601张三1220200701李四1020200601李四1020200702张三1120200602张三1120200702李四2020200602李四2020200701张三2020200601张三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函数可以是其他计算函数 收藏(0)