SQL 查询和优化（七）

行转列
常用方法有CASE WHEN THEN 和 PIVOT函数两种方法

查询需要，对emp表按job分组汇总，每个部门显示为一列

SELECT job AS 工作,
        SUM (CASE deptno WHEN '10' THEN sal END) AS 部门10工资
        SUM (CASE deptno WHEN '20' THEN sal END) AS 部门20工资
        SUM (CASE deptno WHEN '30' THEN sal END) AS 部门30工资
        SUM (sal) AS 总工资
    FROM emp
    GROUP BY job
    ORDER BY 1;

PIVOT方法相对简单,语法如下
SELECT ....  
FROM <table-expr>  
   PIVOT  
     (  
      aggregate-function(<column>)  
      FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)  
        ) AS <alias>  
WHERE .....  
实例
SELECT * 
    FROM (SELECT job,deptno,sal FROM emp)
    POVIT ( SUM(sal) AS s
        FOR deptno 
        IN(10 AS d10,20,30 AS d30)
    )
ORDER BY 1;

如果还要增加提成的返回，用PIVOT则只需要增加一个设定
SELECT * 
    FROM (SELECT job,deptno,sal，comm FROM emp)
    POVIT ( SUM(sal) AS s,SUM(comm) AS c
        FOR deptno 
        IN(10 AS d10,20,30 AS d30)
    )
ORDER BY 1;

而CASE WHEN THEN则要增加三个语句
SELECT job AS 工作,
        SUM (CASE deptno WHEN '10' THEN sal END) AS 部门10工资
        SUM (CASE deptno WHEN '20' THEN sal END) AS 部门20工资
        SUM (CASE deptno WHEN '30' THEN sal END) AS 部门30工资
        SUM (CASE deptno WHEN '10' THEN comm END) AS 部门10提成
        SUM (CASE deptno WHEN '20' THEN comm END) AS 部门20提成
        SUM (CASE deptno WHEN '30' THEN comm END) AS 部门30提成
    FROM emp
    GROUP BY job
    ORDER BY 1;

选自《Oracle 查询优化改写技巧与案例》 有教无类 落落 著