墨墨导读:本文来自墨天轮用户“只是甲”的投稿,总结所有Oracle行转列的语法,供大家参考学习。
墨天轮主页:https://www.modb.pro/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;