Oracle12C--行列转换（十五）

• 基础示例
  • 示例：查询每个部门中各个职位的总工资
  • 分析：本程序所要查询的并不是一个部门的总工资，而是需要统计出各个职位的信息，最简单的做法是直接按照部门编号及职位进行分组统计，于是有了以下的查询语句
    • 步骤1：按照部门编号及职位进行分组。此步骤进行了多表连接而后进行数据分组的方式完成的。但这种数据显示的方式不适合用户浏览，因为数据没有规律，而对于数据最好的浏览方式是像普通数据表那样，按照行的方式列出每一种职位的总工资。

select deptno,job,sum(sal) from emp group by deptno,job;

• 步骤2：针对不同的职位，应该使用不同的sal内容进行求和的统计，此时只能利用decode()函数完成判断。同时为了让多条记录在同一行上显示，可以针对每一个职位分别统计，对于该职位信息的部门应该使用0进行处理；

SELECT deptno, SUM(DECODE(job,'PRESIDENT',sal,0)) president_job, SUM(DECODE(job,'MANAGER',sal,0)) manager_job, SUM(DECODE(job,'ANALYST',sal,0)) analyst_job, SUM(DECODE(job,'CLERK',sal,0)) clerk_job, SUM(DECODE(job,'SALESMAN',sal,0)) salesman_job from emp group BY deptno;

• 步骤2中的decode函数是Oracle的专有函数，如果不使用此函数的话，就要通过如下的子查询方式来做了

SELECT temp.dno, SUM(president_job) , SUM(manager_job) , SUM(analyst_job) , SUM(clerk_job) , SUM(salesman_job) FROM ( SELECT deptno dno , (SELECT SUM(sal) FROM emp WHERE job='PRESIDENT' AND empno=e.empno) PRESIDENT_JOB , (SELECT SUM(sal) FROM emp WHERE job='MANAGER' AND empno=e.empno) MANAGER_JOB , (SELECT SUM(sal) FROM emp WHERE job='ANALYST' AND empno=e.empno) ANALYST_JOB , (SELECT SUM(sal) FROM emp WHERE job='CLERK' AND empno=e.empno) CLERK_JOB , (SELECT SUM(sal) FROM emp WHERE job='SALESMAN' AND empno=e.empno) SALESMAN_JOB FROM emp e ) temp GROUP BY temp.dno ORDER BY temp.dno DESC ;

• 此时所完成的是一个基本的行列转换操作功能，并且显示的记录结果也更加清晰，但代码过于复杂；

• pivot()函数
  • 语法

select * | 列 [别名]... from 子查询 pivot( 统计函数(列)s for转换列名称 in( 内容 1 [ [as]别名], 内容 2 [ [as]别名], ... 内容 n [ [as] 别名] ) ) [where 条件(s)] [group by 分组字段1,分组字段2,....] [having 过滤条件(s)] [order by 排序字段 asc | desc];

核心组成说明如下： 子查询：此处规定了在PIVOT()函数操作过程中，所需要使用到的数据（设置子查询确定行和列）； 统计函数（列）：在转换过程中，设置要进行统计的数据列及统计函数，可以设置多个统计函数； for 转换列名称：将子查询中返回的指定数据变为显示的列；

• 示例1：利用pivot()函数实现转换

SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS president_job , 'MANAGER' AS manager_job , 'ANALYST' AS analyst_job , 'CLERK' AS clerk_job , 'SALESMAN' AS salesman_job ) ) ORDER BY deptno ;

• 拓展：使用XML与ANY
  • 如果在使用pivot()函数时增加了XML显示，可以利用ANY设置所要操作的所有数据

SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT XML ( SUM(sal) FOR job IN (ANY) ) ORDER BY deptno ;

• 这样就会以XML的形式显示数据，不过ANY只能用于PIVOT XML操作里，并不能用于之前的PIVOT()函数中

• 示例2：使用PIVOT()函数只能够完成一种信息的统计，在了解了不同职位的总工资之外，还希望知道部门的人数及最高和最低工资，就需要利用OVER PARTITION BY语句完成

SELECT * FROM ( SELECT job ,deptno , sal, SUM(sal) OVER(PARTITION BY deptno) sum_sal , MAX(sal) OVER(PARTITION BY deptno) max_sal , MIN(sal) OVER(PARTITION BY deptno) min_sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS president_job , 'MANAGER' AS manager_job , 'ANALYST' AS analyst_job , 'CLERK' AS clerk_job , 'SALESMAN' AS salesman_job ) ) ORDER BY deptno ;

• 示例3：设置多个统计函数，查询每个部门不同职位的总工资，以及每个部门不同职位ide最高工资

SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) AS sum_sal , MAX(sal) AS sum_max FOR job IN ( 'PRESIDENT' AS president_job , 'MANAGER' AS manager_job , 'ANALYST' AS analyst_job , 'CLERK' AS clerk_job , 'SALESMAN' AS salesman_job ) ) ORDER BY deptno ;

• unpivot()函数
  • 首先，使用下面的sql语句更新基础代码：

ALTER TABLE emp ADD (sex VARCHAR2(10) DEFAULT '男') ; UPDATE emp SET sex='女' WHERE TO_CHAR(hiredate,'yyyy')='1981' ; COMMIT ;

• 示例1：设置多个统计列。上面的代码更新后，会在emp表中增加一个性别列，现在要求使用PIVOT()函数针对不同职位的不同性别进行总工资的统计，可以在for语句中设置多个列

SELECT * FROM (SELECT deptno , job , sal , sex FROM emp) PIVOT ( SUM(sal) AS sum_sal , MAX(sal) AS sum_max FOR (job, sex) IN ( ('MANAGER','男') AS manager_male_JOB , ('MANAGER','女') AS manager_female_JOB , ('CLERK','男') AS clerk_male_JOB , ('CLERK','女') AS clerk_female_JOB ) ) ORDER BY deptno ;

• 通过PIVOT()函数可以将行转换为列，反过来，也可以使用UNPIVOT()函数将列重新转换为行

• UNPIVOT()函数语法：

select * | 列 [别名]... from 子查询 unpivot [include nulls | exclude nulls]( 统计函数（列）s for转换列名称 in( 内容1 [[as]别名], 内容2 [[as]别名], .... 内容n [[as]别名], ) ) [where 条件(s)] [group by 分组字段1,分组字段2,....] [having 过滤条件(s)] [order by 排序字段 asc|desc];

此函数与pivot()定义类似，不同的地方在于两个选项； INCLUDE NULLS：列变为行转换之后保留所有的null数据； EXCLUDE NULLS(默认)：列变为行转换之后不保留null数据；

• 示例2：

WITH temp AS ( SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS PRESIDENT_JOB , 'MANAGER' AS MANAGER_JOB , 'ANALYST' AS ANALYST_JOB , 'CLERK' AS CLERK_JOB , 'SALESMAN' AS SALESMAN_JOB ) ) ORDER BY deptno ) SELECT * FROM temp UNPIVOT ( sal_sum FOR job IN ( president_job AS 'PRESIDENT' , manager_job AS 'MANAGER' , analyst_job AS 'ANALYST' , clerk_job AS 'CLERK' , salesman_job AS 'SALESMAN' ) ) ORDER BY deptno ;

• 示例3：示例2的查询不包含Null值，可以使用INCLUDE NULLS选项包含null值

WITH temp AS ( SELECT * FROM (SELECT deptno , job , sal FROM emp) PIVOT ( SUM(sal) FOR job IN ( 'PRESIDENT' AS PRESIDENT_JOB , 'MANAGER' AS MANAGER_JOB , 'ANALYST' AS ANALYST_JOB , 'CLERK' AS CLERK_JOB , 'SALESMAN' AS SALESMAN_JOB ) ) ORDER BY deptno ) SELECT * FROM temp UNPIVOT INCLUDE NULLS( sal_sum FOR job IN ( president_job AS 'PRESIDENT' , manager_job AS 'MANAGER' , analyst_job AS 'ANALYST' , clerk_job AS 'CLERK' , salesman_job AS 'SALESMAN' ) ) ORDER BY deptno ;