SQL 查询和优化（七）

来源：互联网发布：淘宝外卖网址编辑：程序博客网时间：2024/06/06 03:18

行转列
常用方法有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 查询优化改写技巧与案例》有教无类落落著

阅读全文

0 0