【Oracle】增强 group by 子句
增强 group by 子句的使用频率虽然不高，但作为开发人员一定要有所了解，也就是得知道有这个东西，只有在知道的情况下，后续的管理、设计、开发才会有更大的空间。
《续》 http://www.xxf-home.net/archives/725
1：rollback扩展
需求：如果只想统计每个部门的岗位工资总和及公司的工资总和，那么该怎么计算呢？两个SQL的union all吗？显然不是，Oracle给出来非常巧妙的解决办法，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
## 我们仔细看下面的SQL，很巧妙，ROLLUP后是双括号 SCOTT@ORA11GR2> SELECT decode(nvl(deptno, '0'), '0', 'all deptno', deptno) deptno, decode(nvl(job, '0'), '0', 'all job', job) job, SUM(sal) sal FROM emp GROUP BY ROLLUP((deptno, job)); DEPTNO JOB SAL --------------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 CLERK 1900 20 ANALYST 6000 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 all deptno all job 29025 10 rows selected. SCOTT@ORA11GR2> ## 试想一下，如下SQL的结果是什么 SELECT decode(nvl(deptno, '0'), '0', 'all deptno', deptno) deptno, decode(nvl(job, '0'), '0', 'all job', job) job, SUM(sal) sal FROM emp GROUP BY ROLLUP (deptno),job; |
2：cube，用法与rollup相同，测试如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
## 读者可以很清晰的读懂以下SQL，并了解cube与rollup的区别 SCOTT@ORA11GR2> SELECT deptno, job, SUM(sal) FROM emp GROUP BY CUBE(deptno, job) ORDER BY 1; DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 9400 ANALYST 6000 CLERK 4150 MANAGER 8275 PRESIDENT 5000 SALESMAN 5600 29025 18 rows selected. SCOTT@ORA11GR2> ## 以上SQL等价于 SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno, job UNION ALL SELECT deptno, to_char(NULL), SUM(sal) FROM emp GROUP BY deptno UNION ALL SELECT NULL, job, SUM(sal) FROM emp GROUP BY job UNION ALL SELECT NULL, NULL, SUM(sal) FROM emp ORDER BY 1, 2; |
3：cube双括号等价于rollup双括号
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SCOTT@ORA11GR2> SELECT deptno, job, SUM(sal) FROM emp GROUP BY CUBE((deptno, job)) ORDER BY 1; DEPTNO JOB SUM(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 29025 10 rows selected. SCOTT@ORA11GR2> |
4：增强 group by 子句还有好多相关函数，但多数几乎没有利用价值，就不占用本章节篇幅了。