select job,
nvl(sum(decode(deptno,10,sal)),0) dept10,
nvl(sum(decode(deptno,20,sal)),0) dept20,
nvl(sum(decode(deptno,30,sal)),0) dept30,
sum(decode(deptno,10,sal))+sum(decode(deptno,20,sal))+sum(decode(deptno,30,sal)) total
from emp
group by job;
nvl(sum(decode(deptno,10,sal)),0) dept10,
nvl(sum(decode(deptno,20,sal)),0) dept20,
nvl(sum(decode(deptno,30,sal)),0) dept30,
sum(decode(deptno,10,sal))+sum(decode(deptno,20,sal))+sum(decode(deptno,30,sal)) total
from emp
group by job;
9i以后
case when then else end
http://hi.baidu.com/zhao_e893/blog/item/1e18224eec57c8cad1c86a3f.html这个是为oracle写的,当然稍做改动,也可以适合所有的sql
对,就是这样
可是查询结果为什么是
JOB DEPT10 DEPT20 DEPT30 TOTAL
--------- ---------- ---------- ---------- ----------
0 0 0
CLERK 1300 1900 950 4150
SALESMAN 0 0 4000
PRESIDENT 5000 0 0
MANAGER 2450 2975 2850 8275
ANALYST 0 6000 0
有NULL的TOTAL也为null了
select job,
nvl(sum(decode(deptno,10,sal)),0) dept10,
nvl(sum(decode(deptno,20,sal)),0) dept20,
nvl(sum(decode(deptno,30,sal)),0) dept30,
nvl(sum(decode(deptno,10,sal)),0)+
nvl(sum(decode(deptno,20,sal)),0)+
nvl(sum(decode(deptno,30,sal)),0) total
from emp
group by job;
就行了
sum +null的话也是null
select job,sum(case when deptno=10 then sal else 0 end) deptno10,
sum(case when deptno=20 then sal else 0 end) deptno20,
sum(case when deptno=30 then sal else 0 end) deptno30,
sum(case when deptno=10 then sal else 0 end)+
sum(case when deptno=20 then sal else 0 end)+
sum(case when deptno=30 then sal else 0 end) total
from emp group by job;哈,接受意见,以前没用过,现在用了一下.