Liny_@NotePad

沉迷ACG中

SQL练习

【SCOTT/TIGER】

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

  1. SELECT deptno DEPARTMENT_ID,
  2.        COUNT(*) "部门人数",
  3.        SUM(DECODE(SIGN(sal-9000),-1,0,1)) "超高收入人数(>9000)",
  4.        SUM(DECODE(SIGN(sal-9000),-1,0,( DECODE(SIGN(sal-12000),1,0,1) ) )) "极高收入人数([9000,12000])",
  5.        SUM(DECODE(SIGN(sal-6000),-1,0,( DECODE(SIGN(sal-9000),1,0,1) ) )) "高收入([6000,9000])",
  6.        SUM(DECODE(SIGN(sal-3000),-1,0,( DECODE(SIGN(sal-6000),1,0,1) ) )) "一般收入([3000,6000])",
  7.        SUM(DECODE(SIGN(sal-3000),-1,1,0)) "低收入([<3000])"
  8.   FROM emp
  9.  GROUP BY deptno;