墨天轮主页:https://www./u/372619 注:本文测试以Oracle 11g下的scoot schema为例。 需求:求emp表各个岗位的工资之和,若无,用0代替。 一、decode语法SELECT deptno, nvl(SUM(decode(job, 'MANAGER', sal)), 0) s_MANAGER, nvl(SUM(decode(job, 'ANALYST', sal)), 0) s_ANALYST, nvl(SUM(decode(job, 'CLERK', sal)), 0) s_CLERK, nvl(SUM(decode(job, 'PRESIDENT', sal)), 0) s_PRESIDENT, nvl(SUM(decode(job, 'SALESMAN', sal)), 0) s_SALESMAN FROM emp GROUP BY deptno; 二、CASE语法SELECT deptno, nvl(sum(case when job = 'MANAGER' then sal else 0 end),0) s_MANAGER, nvl(sum(case when job = 'ANALYST' then sal else 0 end),0) s_ANALYST, nvl(sum(case when job = 'CLERK' then sal else 0 end),0) s_CLERK, nvl(sum(case when job = 'PRESIDENT' then sal else 0 end),0) s_PRESIDENT, nvl(sum(case when job = 'SALESMAN' then sal else 0 end),0) s_SALESMAN FROM emp GROUP BY deptno; 三、PIVOT语法WITH p AS (SELECT deptno, job, sal FROM emp) SELECT * FROM p pivot(SUM(sal) FOR job IN('MANAGER' AS s_MANAGER, 'ANALYST' AS s_ANALYST, 'CLERK' AS s_CLERK, 'PRESIDENT' AS s_PRESIDENT, 'SALESMAN' AS s_SALESMAN)); 不过这个地方null值没有替换成0,要通过nvl再转换一下。 WITH p AS (SELECT deptno, job, sal FROM emp), tmp AS (SELECT * FROM p pivot(SUM(sal) FOR job IN('MANAGER' AS s_MANAGER, 'ANALYST' AS s_ANALYST, 'CLERK' AS s_CLERK, 'PRESIDENT' AS s_PRESIDENT, 'SALESMAN' AS s_SALESMAN))) SELECT deptno, nvl(s_MANAGER, 0) s_MANAGER, nvl(s_ANALYST, 0) s_ANALYST, nvl(s_CLERK, 0) s_CLERK, nvl(s_PRESIDENT, 0) s_PRESIDENT, nvl(s_SALESMAN, 0) s_SALESMAN FROM tmp 小结:decode 语法简单,Oracle独有。 下面再来讲讲wm_concat、listagg、xmlagg。 需求:部门编号为20的所有的员工信息,以行的形式显示。 四、wm_contact语法SELECT T.DEPTNO, wm_concat(t.ename) names FROM EMP T WHERE T.DEPTNO = '20' GROUP BY T.DEPTNO; 五、listagg语法SELECT T.DEPTNO, listagg(T.ENAME, ',') WITHIN GROUP(ORDER BY T .ENAME) names FROM EMP T WHERE T.DEPTNO = '20' GROUP BY T.DEPTNO; 六、xmlagg语法SELECT T.DEPTNO, xmlagg(XMLELEMENT(T, ',',T.ENAME) ORDER BY T .ENAME).EXTRACT ('//text()') names FROM EMP T WHERE T.DEPTNO = '20' GROUP BY T.DEPTNO; 小结:wm_concat 语法最简单,但是默认是 clob列,plsql不容易导出。 作者 曾庆顺,10年数据库运维、数据仓库及大数据经验,擅长Oracle、MySQL、Hive,具有Oracle 10g OCP,Linux RHCE,长期服务于通信、金融信贷行业。 墨天轮原文链接:https://www./db/26033(复制到浏览器打开或者点击“阅读原文”立即查看) |
|