备注:测试以Oracle 11g下的scoot schema为例
需求:求emp表各个岗位的工资之和,如无,用0代替
如需要scott用户下建表及录入数据语句,可参考:
scott建表及录入数据sql脚本
一.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;
image.png
二.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;
image.png
三.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));
image.png
不过这个地方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
image.png
小结:
decode 语法简单,Oracle独有
case sql标准语法
pivot 语法最为简单,Oracle、sqlserver、postgresql均可以使用
下面再来讲讲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;
image.png
五.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;
image.png
六.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;
image.png
小结:
wm_concat 语法最简单,但是默认是 clob列,plsql不容易导出
listtagg 语法稍微复杂,但是默认是字符串,性能会比wm_concat 好,但是超过4000个字符,受限制。
xmlagg 字符串超过4000字符,就需要使用xmlagg