SQL练习

【SCOTT/TIGER】

7. 用Decode函数实现横表和竖表的转换

SELECT deptno DEPARTMENT_ID,
COUNT(*) "部门人数",
SUM(DECODE(SIGN(sal-9000),-1,0,1)) "超高收入人数（>9000）",
SUM(DECODE(SIGN(sal-9000),-1,0,( DECODE(SIGN(sal-12000),1,0,1) ) )) "极高收入人数（[9000,12000]）",
SUM(DECODE(SIGN(sal-6000),-1,0,( DECODE(SIGN(sal-9000),1,0,1) ) )) "高收入（[6000,9000]）",
SUM(DECODE(SIGN(sal-3000),-1,0,( DECODE(SIGN(sal-6000),1,0,1) ) )) "一般收入（[3000,6000]）",
SUM(DECODE(SIGN(sal-3000),-1,1,0)) "低收入（[<3000]）"
FROM emp
GROUP BY deptno;