在数据仓库系统中，经常需要对数据进行多维分析，不仅需要标准分组的结果，还需要不同维度的小计和合计，从而提供多角度的数据分析支持，对于这种复杂分组需求，简单 GROUP BY 很难达到这种目的。
Oracle 扩展 GROUP BY 允许使用 SQL 语句对数据汇总结果进行多维展现，从而生成复杂的报表，为决策者提供有效的数据支持。
- ROLLUP、CUBE、GROUPING_SETS 扩展 GROUP BY 子句提供了丰富的多维分组统计功能。
- 3 个扩展分组函数： GROUPING、GROUPING_ID、GROUP_ID 提供扩展 GROUP BY 的辅助功能。例如，提供区别结果行属于哪个分组级别、区分 NULL 值、建立有意义的报表、对汇总结果排序、过滤结果行等功能。
- 对扩展 GROUP BY 允许按重复列分组、组合列分组、部分分组、连接分组等，另外 GROUPING SETS 可以接受 ROLLUP、CUBE 操作作为其参数。
ROLLUP
从 Oracle 8i 开始，Oracle 使用 ROLLUP 对 GROUP BY 进行扩展，它允许计算标准分组及相应维度的小计、合计。
ROLLUP 后面指定的列以逗号分隔，ROLLUP 的计算结果和其后面指定列的顺序有关，因为 ROLLUP 分组过程具有方向性，先计算标准分组，然后列从右到左递减计算更高一级的小计，一直到列全部被选完，最后计算合计。
如果 ROLLUP 中指定 n 列，则整个计算过程中的分组方式有 n + 1 种。
ROLLUP 分组
| 序号 | 分组级别 | SQL 解释 |
| 1 | GROUP BY ROLLUP(A, B, C) | GROUP BY A, B, C |
| 2 | GROUP BY A, B | |
| 3 | GROUP BY A | |
| 4 | GROUP BY NULL |
- GROUP BY ROLLUP(A, B, C)
SQL> alter session set statistics_level = all;
SQL> select
to_char(e.hiredate, 'YYYY') hireyear, d.dname,
e.job, sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by rollup(to_char(e.hiredate, 'YYYY'), d.dname, e.job)
order by 1, 2, 4, 3;
HIREYEAR DNAME JOB SUM_SAL
---------- -------------- --------- ----------
1980 RESEARCH CLERK 800
1980 RESEARCH 800
1980 800
1981 ACCOUNTING MANAGER 2450
1981 ACCOUNTING PRESIDENT 5000
1981 ACCOUNTING 7450
1981 RESEARCH MANAGER 2975
1981 RESEARCH ANALYST 3000
1981 RESEARCH 5975
1981 SALES CLERK 950
1981 SALES MANAGER 2850
1981 SALES SALESMAN 5600
1981 SALES 9400
1981 22825
1982 ACCOUNTING CLERK 1300
1982 ACCOUNTING 1300
1982 1300
1987 RESEARCH CLERK 1100
1987 RESEARCH ANALYST 3000
1987 RESEARCH 4100
1987 4100
29025
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 22 |00:00:00.01 | 8 |
| 1 | SORT ORDER BY | | 1 | 14 | 22 |00:00:00.01 | 8 |
| 2 | SORT GROUP BY ROLLUP | | 1 | 14 | 22 |00:00:00.01 | 8 |
| 3 | MERGE JOIN | | 1 | 14 | 14 |00:00:00.01 | 8 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 4 |00:00:00.01 | 2 |
| 5 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 |
|* 6 | SORT JOIN | | 4 | 14 | 14 |00:00:00.01 | 6 |
| 7 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 6 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
上述 SQL 语句中， ROLLUP 有三列，那么整个计算过程中会有四种分组方式，分别为： GROUP BY TO_CHAR(E.HIREDATE, 'YYYY'), D.DNAME, E.JOB 的标准分组、GROUP BY TO_CHAR(E.HIREDATE, 'YYYY'), D.DNAME 维度的小计、GROUP BY TO_CHAR(E.HIREDATE, 'YYYY') 维度的小计、GROUP BY NULL 维度的合计，为了便于理解，列一张表格来分析结果集。
| 序号 | 相关行 | SQL 解释 | 备注 |
| 1 | 第 2、6、9、13、16、20 行 | GROUP BY TO_CHAR(E.HIREDATE, 'YYYY'), D.DNAME | 计算小计 |
| 2 | 第 3、14、17、21 行 | GROUP BY TO_CHAR(E.HIREDATE, 'YYYY') | 计算小计 |
| 3 | 第 22 行 | GROUP BY NULL | 计算合计 |
- 使用 UNION ALL 实现
SQL> alter session set statistics_level = all;
SQL> select
to_char(e.hiredate, 'YYYY') hireyear, d.dname,
e.job, sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by (to_char(e.hiredate, 'YYYY'), d.dname, e.job)
union all
select
to_char(e.hiredate, 'YYYY') hireyear, d.dname,
null job, sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by (to_char(e.hiredate, 'YYYY'), d.dname, null)
union all
select
to_char(e.hiredate, 'YYYY') hireyear, null dname,
null job, sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by (to_char(e.hiredate, 'YYYY'), null, null)
union all
select
null hireyear, null dname,
null job, sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by (null, null, null)
order by 1, 2, 4, 3;
HIREYEAR DNAME JOB SUM_SAL
---------- -------------- --------- ----------
1980 RESEARCH CLERK 800
1980 RESEARCH 800
1980 800
1981 ACCOUNTING MANAGER 2450
1981 ACCOUNTING PRESIDENT 5000
1981 ACCOUNTING 7450
1981 RESEARCH MANAGER 2975
1981 RESEARCH ANALYST 3000
1981 RESEARCH 5975
1981 SALES CLERK 950
1981 SALES MANAGER 2850
1981 SALES SALESMAN 5600
1981 SALES 9400
1981 22825
1982 ACCOUNTING CLERK 1300
1982 ACCOUNTING 1300
1982 1300
1987 RESEARCH CLERK 1100
1987 RESEARCH ANALYST 3000
1987 RESEARCH 4100
1987 4100
29025
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 22 |00:00:00.01 | 28 |
| 1 | SORT ORDER BY | | 1 | 55 | 22 |00:00:00.01 | 28 |
| 2 | UNION-ALL | | 1 | | 22 |00:00:00.01 | 28 |
| 3 | HASH GROUP BY | | 1 | 14 | 11 |00:00:00.01 | 8 |
| 4 | MERGE JOIN | | 1 | 14 | 14 |00:00:00.01 | 8 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 4 |00:00:00.01 | 2 |
| 6 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 |
|* 7 | SORT JOIN | | 4 | 14 | 14 |00:00:00.01 | 6 |
| 8 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 6 |
| 9 | HASH GROUP BY | | 1 | 14 | 6 |00:00:00.01 | 8 |
| 10 | MERGE JOIN | | 1 | 14 | 14 |00:00:00.01 | 8 |
| 11 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 4 |00:00:00.01 | 2 |
| 12 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 |
|* 13 | SORT JOIN | | 4 | 14 | 14 |00:00:00.01 | 6 |
| 14 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 6 |
| 15 | HASH GROUP BY | | 1 | 13 | 4 |00:00:00.01 | 6 |
|* 16 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 6 |
| 17 | SORT GROUP BY NOSORT | | 1 | 14 | 1 |00:00:00.01 | 6 |
|* 18 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 6 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
13 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
16 - filter("E"."DEPTNO" IS NOT NULL)
18 - filter("E"."DEPTNO" IS NOT NULL)
部分 ROLLUP 分组
普通 ROLLUP 分组，含有标准分组、多种小计、合计。Oracle 提供了部分 ROLLUP 分组功能，也就是将部分列从 ROLLUP 中移出来，放在 GROUP BY 中，这样合计肯定没有了，某些小计也没有了。
| 序号 | 分组级别 | SQL 解释 |
| 1 | GROUP BY A, ROLLUP(B, C) | GROUP BY A, B, C |
| 2 | GROUP BY A, B | |
| 3 | GROUP BY A | |
| 4 | GROUP BY A, B, ROLLUP(C) | GROUP BY A, B, C |
| 5 | GROUP BY A, B |
- GROUP BY A, ROLLUP(B, C)
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job, sum(e.sal) sum_sal from dept d, emp e where d.deptno = e.deptno group by to_char(e.hiredate, 'YYYY'), rollup(d.dname, e.job) order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH CLERK 800 1980 RESEARCH 800 1980 800 1981 ACCOUNTING MANAGER 2450 1981 ACCOUNTING PRESIDENT 5000 1981 ACCOUNTING 7450 1981 RESEARCH MANAGER 2975 1981 RESEARCH ANALYST 3000 1981 RESEARCH 5975 1981 SALES CLERK 950 1981 SALES MANAGER 2850 1981 SALES SALESMAN 5600 1981 SALES 9400 1981 22825 1982 ACCOUNTING CLERK 1300 1982 ACCOUNTING 1300 1982 1300 1987 RESEARCH CLERK 1100 1987 RESEARCH ANALYST 3000 1987 RESEARCH 4100 1987 4100
- 使用 UNION ALL 实现
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), d.dname, e.job) union all select to_char(e.hiredate, 'YYYY') hireyear, d.dname, null job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), d.dname, null) union all select to_char(e.hiredate, 'YYYY') hireyear, null dname, null job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), null, null) order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH CLERK 800 1980 RESEARCH 800 1980 800 1981 ACCOUNTING MANAGER 2450 1981 ACCOUNTING PRESIDENT 5000 1981 ACCOUNTING 7450 1981 RESEARCH MANAGER 2975 1981 RESEARCH ANALYST 3000 1981 RESEARCH 5975 1981 SALES CLERK 950 1981 SALES MANAGER 2850 1981 SALES SALESMAN 5600 1981 SALES 9400 1981 22825 1982 ACCOUNTING CLERK 1300 1982 ACCOUNTING 1300 1982 1300 1987 RESEARCH CLERK 1100 1987 RESEARCH ANALYST 3000 1987 RESEARCH 4100 1987 4100
- GROUP BY A, B, ROLLUP(C)
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job, sum(e.sal) sum_sal from dept d, emp e where d.deptno = e.deptno group by to_char(e.hiredate, 'YYYY'), d.dname, rollup(e.job) order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH CLERK 800 1980 RESEARCH 800 1981 ACCOUNTING MANAGER 2450 1981 ACCOUNTING PRESIDENT 5000 1981 ACCOUNTING 7450 1981 RESEARCH MANAGER 2975 1981 RESEARCH ANALYST 3000 1981 RESEARCH 5975 1981 SALES CLERK 950 1981 SALES MANAGER 2850 1981 SALES SALESMAN 5600 1981 SALES 9400 1982 ACCOUNTING CLERK 1300 1982 ACCOUNTING 1300 1987 RESEARCH CLERK 1100 1987 RESEARCH ANALYST 3000 1987 RESEARCH 4100
- 使用 UNION ALL 实现
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), d.dname, e.job) union all select to_char(e.hiredate, 'YYYY') hireyear, d.dname, null job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), d.dname, null) order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH CLERK 800 1980 RESEARCH 800 1981 ACCOUNTING MANAGER 2450 1981 ACCOUNTING PRESIDENT 5000 1981 ACCOUNTING 7450 1981 RESEARCH MANAGER 2975 1981 RESEARCH ANALYST 3000 1981 RESEARCH 5975 1981 SALES CLERK 950 1981 SALES MANAGER 2850 1981 SALES SALESMAN 5600 1981 SALES 9400 1982 ACCOUNTING CLERK 1300 1982 ACCOUNTING 1300 1987 RESEARCH CLERK 1100 1987 RESEARCH ANALYST 3000 1987 RESEARCH 4100
CUBE
CUBE 比 ROLLUP 更加精细，包含了 ROLLUP 的统计结果，而且还有其它组合分组结果（小计），CUBE 计算结果和列的顺序无关，如果列顺序不同，默认的结果排序则不同。
如果 CUBE 中指定 n 列，则整个计算过程中的分组方式有 2 ^ n 种。
CUBE 分组
| 序号 | 分组级别 | SQL 解释 |
| 1 | GROUP BY CUBE(A, B) | GROUP BY A, B |
| 2 | GROUP BY A | |
| 3 | GROUP BY B | |
| 4 | GROUP BY NULL |
- GROUP BY CUBE(A, B)
SQL> select
d.dname, e.job, sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by cube(d.dname, e.job)
order by 1, 2, 3;
DNAME JOB SUM_SAL
-------------- --------- ----------
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 8750
RESEARCH ANALYST 6000
RESEARCH CLERK 1900
RESEARCH MANAGER 2975
RESEARCH 10875
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025
上述 SQL 语句中， CUBE 有二列，那么整个计算过程中会有四种分组方式，分别为： GROUP BY D.DNAME, E.JOB 的标准分组、GROUP BY D.DNAME 维度的小计、GROUP BY E.JOB 维度的小计、GROUP BY NULL, 维度的合计，为了便于理解，列一张表格来分析结果集。
| 序号 | 相关行 | SQL 解释 | 备注 |
| 1 | 第 1、2、3 行 | GROUP BY D.DNAME, E.JOB | 标准分组 |
| 2 | 第 4、8、12 行 | GROUP BY D.DNAME | 计算小计 |
| 3 | 第 13、14、15、16、17 行 | GROUP BY E.JOB | 计算小计 |
| 4 | 第 18 行 | GROUP BY NULL | 计算合计 |
- 使用 UNION ALL 实现
SQL> select
d.dname, e.job, sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by (d.dname, e.job)
union all
select
d.dname, null job, sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by (d.dname, null)
union all
select
null dname, e.job, sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by (null, e.job)
union all
select
null dname, null job, sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by (null, null)
order by 1, 2, 3;
DNAME JOB SUM_SAL
-------------- --------- ----------
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 8750
RESEARCH ANALYST 6000
RESEARCH CLERK 1900
RESEARCH MANAGER 2975
RESEARCH 10875
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025
部分 CUBE 分组
和 ROLLUP 一样，也有部分 CUBE 操作，可以去掉合计及某些不需要的小计。
| 序号 | 分组级别 | SQL 解释 |
| 1 | GROUP BY A, CUBE(B, C) | GROUP BY A, B, C |
| 2 | GROUP BY A, B | |
| 3 | GROUP BY A, C | |
| 4 | GROUP BY A | |
| 5 | GROUP BY A, B, CUBE(C) | GROUP BY A, B, C |
| 6 | GROUP BY A, B |
- GROUP BY A, CUBE(B, C)
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job, sum(e.sal) sum_sal from dept d, emp e where d.deptno = e.deptno group by to_char(e.hiredate, 'YYYY'), cube(d.dname, e.job) order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH CLERK 800 1980 RESEARCH 800 1980 CLERK 800 1980 800 1981 ACCOUNTING MANAGER 2450 1981 ACCOUNTING PRESIDENT 5000 1981 ACCOUNTING 7450 1981 RESEARCH MANAGER 2975 1981 RESEARCH ANALYST 3000 1981 RESEARCH 5975 1981 SALES CLERK 950 1981 SALES MANAGER 2850 1981 SALES SALESMAN 5600 1981 SALES 9400 1981 CLERK 950 1981 ANALYST 3000 1981 PRESIDENT 5000 1981 SALESMAN 5600 1981 MANAGER 8275 1981 22825 1982 ACCOUNTING CLERK 1300 1982 ACCOUNTING 1300 1982 CLERK 1300 1982 1300 1987 RESEARCH CLERK 1100 1987 RESEARCH ANALYST 3000 1987 RESEARCH 4100 1987 CLERK 1100 1987 ANALYST 3000 1987 4100
- 使用 UNION ALL 实现
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), d.dname, e.job) union all select to_char(e.hiredate, 'YYYY') hireyear, d.dname, null job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), d.dname, null) union all select to_char(e.hiredate, 'YYYY') hireyear, null dname, e.job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), e.job, null) union all select to_char(e.hiredate, 'YYYY') hireyear, null dname, null job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), null, null) order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH CLERK 800 1980 RESEARCH 800 1980 CLERK 800 1980 800 1981 ACCOUNTING MANAGER 2450 1981 ACCOUNTING PRESIDENT 5000 1981 ACCOUNTING 7450 1981 RESEARCH MANAGER 2975 1981 RESEARCH ANALYST 3000 1981 RESEARCH 5975 1981 SALES CLERK 950 1981 SALES MANAGER 2850 1981 SALES SALESMAN 5600 1981 SALES 9400 1981 CLERK 950 1981 ANALYST 3000 1981 PRESIDENT 5000 1981 SALESMAN 5600 1981 MANAGER 8275 1981 22825 1982 ACCOUNTING CLERK 1300 1982 ACCOUNTING 1300 1982 CLERK 1300 1982 1300 1987 RESEARCH CLERK 1100 1987 RESEARCH ANALYST 3000 1987 RESEARCH 4100 1987 CLERK 1100 1987 ANALYST 3000 1987 4100
- GROUP BY A, B, CUBE(C)
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job, sum(e.sal) sum_sal from dept d, emp e where d.deptno = e.deptno group by to_char(e.hiredate, 'YYYY'), d.dname, cube(e.job) order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH CLERK 800 1980 RESEARCH 800 1981 ACCOUNTING MANAGER 2450 1981 ACCOUNTING PRESIDENT 5000 1981 ACCOUNTING 7450 1981 RESEARCH MANAGER 2975 1981 RESEARCH ANALYST 3000 1981 RESEARCH 5975 1981 SALES CLERK 950 1981 SALES MANAGER 2850 1981 SALES SALESMAN 5600 1981 SALES 9400 1982 ACCOUNTING CLERK 1300 1982 ACCOUNTING 1300 1987 RESEARCH CLERK 1100 1987 RESEARCH ANALYST 3000 1987 RESEARCH 4100
- 使用 UNION ALL 实现
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), d.dname, e.job) union all select to_char(e.hiredate, 'YYYY') hireyear, d.dname, null job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), d.dname, null) order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH CLERK 800 1980 RESEARCH 800 1981 ACCOUNTING MANAGER 2450 1981 ACCOUNTING PRESIDENT 5000 1981 ACCOUNTING 7450 1981 RESEARCH MANAGER 2975 1981 RESEARCH ANALYST 3000 1981 RESEARCH 5975 1981 SALES CLERK 950 1981 SALES MANAGER 2850 1981 SALES SALESMAN 5600 1981 SALES 9400 1982 ACCOUNTING CLERK 1300 1982 ACCOUNTING 1300 1987 RESEARCH CLERK 1100 1987 RESEARCH ANALYST 3000 1987 RESEARCH 4100
GROUPING SETS 实现小计
Oracle 9i 开始，提供了 GROUPING SETS，它只对某个单列分组，从而得到指定维度的小计。
n 列的 GROUPING SETS 的分组方式有 n 种。
GROUPING SETS 分组
| 序号 | 分组级别 | SQL 解释 |
| 1 | GROUP BY GROUPING SETS (A, B, C) | GROUP BY A |
| 2 | GROUP BY B | |
| 3 | GROUP BY C |
- GROUP BY GROUPING SETS (A, B, C)
SQL> select
to_char(e.hiredate, 'YYYY') hireyear, d.dname,
e.job, sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by grouping sets (to_char(e.hiredate, 'YYYY'), d.dname, e.job)
order by 1, 2, 4, 3;
HIREYEAR DNAME JOB SUM_SAL
---------- -------------- --------- ----------
1980 800
1981 22825
1982 1300
1987 4100
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
CLERK 4150
PRESIDENT 5000
SALESMAN 5600
ANALYST 6000
MANAGER 8275
- 使用 UNION ALL 实现
SQL> select
to_char(e.hiredate, 'YYYY') hireyear, null dname,
null job, sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by (to_char(e.hiredate, 'YYYY'), null, null)
union all
select
null hireyear, d.dname,
null job, sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by (null, d.dname, null)
union all
select
null hireyear, null dname,
e.job job, sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by (null, null, e.job)
order by 1, 2, 4, 3;
HIREYEAR DNAME JOB SUM_SAL
---------- -------------- --------- ----------
1980 800
1981 22825
1982 1300
1987 4100
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
CLERK 4150
PRESIDENT 5000
SALESMAN 5600
ANALYST 6000
MANAGER 8275
部分 GROUPING SETS 分组
每种扩展 GROUP BY 都有部分分组特性，GROUPING SETS 也不例外。
| 序号 | 分组级别 | SQL 解释 |
| 1 | GROUP BY A GROUPING SETS(B, C) | GROUP BY A, B |
| 2 | GROUP BY A, C | |
| 3 | GROUP BY A, B, ROUPING SETS(C) | GROUP BY A, B, C |
- GROUP BY A GROUPING SETS(B, C)
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job, sum(e.sal) sum_sal from dept d, emp e where d.deptno = e.deptno group by to_char(e.hiredate, 'YYYY'), grouping sets(d.dname, e.job) order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH 800 1980 CLERK 800 1981 ACCOUNTING 7450 1981 RESEARCH 5975 1981 SALES 9400 1981 CLERK 950 1981 ANALYST 3000 1981 PRESIDENT 5000 1981 SALESMAN 5600 1981 MANAGER 8275 1982 ACCOUNTING 1300 1982 CLERK 1300 1987 RESEARCH 4100 1987 CLERK 1100 1987 ANALYST 3000
- 使用 UNION ALL 实现
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, null job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), d.dname) union all select to_char(e.hiredate, 'YYYY') hireyear, null dname, e.job job, sum(e.sal) sum_sal from emp e, dept d where e.deptno = d.deptno group by (to_char(e.hiredate, 'YYYY'), e.job) order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH 800 1980 CLERK 800 1981 ACCOUNTING 7450 1981 RESEARCH 5975 1981 SALES 9400 1981 CLERK 950 1981 ANALYST 3000 1981 PRESIDENT 5000 1981 SALESMAN 5600 1981 MANAGER 8275 1982 ACCOUNTING 1300 1982 CLERK 1300 1987 RESEARCH 4100 1987 CLERK 1100 1987 ANALYST 3000
- GROUP BY A GROUPING SETS(B, C)
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job, sum(e.sal) sum_sal from dept d, emp e where d.deptno = e.deptno group by to_char(e.hiredate, 'YYYY'), d.dname, grouping sets(e.job) order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH CLERK 800 1981 ACCOUNTING MANAGER 2450 1981 ACCOUNTING PRESIDENT 5000 1981 RESEARCH MANAGER 2975 1981 RESEARCH ANALYST 3000 1981 SALES CLERK 950 1981 SALES MANAGER 2850 1981 SALES SALESMAN 5600 1982 ACCOUNTING CLERK 1300 1987 RESEARCH CLERK 1100 1987 RESEARCH ANALYST 3000
- 使用 UNION ALL 实现
SQL> select to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job, sum(e.sal) sum_sal from dept d, emp e where d.deptno = e.deptno group by to_char(e.hiredate, 'YYYY'), d.dname, e.job order by 1, 2, 4, 3; HIREYEAR DNAME JOB SUM_SAL ---------- -------------- --------- ---------- 1980 RESEARCH CLERK 800 1981 ACCOUNTING MANAGER 2450 1981 ACCOUNTING PRESIDENT 5000 1981 RESEARCH MANAGER 2975 1981 RESEARCH ANALYST 3000 1981 SALES CLERK 950 1981 SALES MANAGER 2850 1981 SALES SALESMAN 5600 1982 ACCOUNTING CLERK 1300 1987 RESEARCH CLERK 1100 1987 RESEARCH ANALYST 3000
ROLLUP、CUBE 作为 GROUPING SETS 的参数
GROUPING SETS 操作能够接受 ROLLUP 和 CUBE 作为它的参数，GROUPING SETS 操作只对单列进行分组，而不提供合计的功能，如果需要 GROUPING SETS 提供合计的功能同时又不需要标准分组，那么可以使用 ROLLUP 或 CUBE 作为 GROUPING SETS 的参数。
SQL> select
to_char(e.hiredate, 'YYYY') hireyear, d.dname,
e.job, sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by grouping sets(rollup(to_char(e.hiredate, 'YYYY')), rollup(d.dname), rollup(e.job))
order by 1, 2, 4, 3;
HIREYEAR DNAME JOB SUM_SAL
---------- -------------- --------- ----------
1980 800
1981 22825
1982 1300
1987 4100
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
CLERK 4150
PRESIDENT 5000
SALESMAN 5600
ANALYST 6000
MANAGER 8275
29025
29025
29025
上述 SQL 语句产生了三个合计行，因为 ROLLUP 或 CUBE 作为 GROUPING SETS 的参数，相当于对每个 ROLLUP 或 CUBE 操作的 UNION ALL：
SQL> select
to_char(e.hiredate, 'YYYY') hireyear, null dname,
null job, sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by grouping sets(rollup(to_char(e.hiredate, 'YYYY')))
union all
select
null hireyear, d.dname,
null job, sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by grouping sets(rollup(d.dname))
union all
select
null hireyear, null dname,
e.job job, sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by grouping sets(rollup(e.job))
order by 1, 2, 4, 3;
HIREYEAR DNAME JOB SUM_SAL
---------- -------------- --------- ----------
1980 800
1981 22825
1982 1300
1987 4100
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
CLERK 4150
PRESIDENT 5000
SALESMAN 5600
ANALYST 6000
MANAGER 8275
29025
29025
29025
需要注意的是，ROLLUP 和 CUBE 不能接受 GEOUPING SETS 作为参数，ROLLUP 和 CUBE 之间互相作为参数也不可以。
组合列分组
组合列也就是将多个列用括号括起来，从而将多个列当做整体对待，组合列分组有过滤某些小计或计算额外的小计等功能。
| 序号 | 分组级别 | SQL 解释 |
| 1 | GROUP BY ROLLUP(A, B, C) | GROUP BY A, B, C |
| 2 | GROUP BY A, B | |
| 3 | GROUP BY A | |
| 4 | GROUP BY NULL | |
| 5 | GROUP BY ROLLUP(A, (B, C)) | GROUP BY A, B, C |
| 6 | GROUP BY A | |
| 7 | GROUP BY NULL |
- GROUP BY ROLLUP(A, B, C)
SQL> select
to_char(e.hiredate, 'YYYY') hireyear, d.dname,
e.job, sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by rollup(to_char(e.hiredate, 'YYYY'), d.dname, e.job)
order by 1, 2, 4, 3;
HIREYEAR DNAME JOB SUM_SAL
---------- -------------- --------- ----------
1980 RESEARCH CLERK 800
1980 RESEARCH 800
1980 800
1981 ACCOUNTING MANAGER 2450
1981 ACCOUNTING PRESIDENT 5000
1981 ACCOUNTING 7450
1981 RESEARCH MANAGER 2975
1981 RESEARCH ANALYST 3000
1981 RESEARCH 5975
1981 SALES CLERK 950
1981 SALES MANAGER 2850
1981 SALES SALESMAN 5600
1981 SALES 9400
1981 22825
1982 ACCOUNTING CLERK 1300
1982 ACCOUNTING 1300
1982 1300
1987 RESEARCH CLERK 1100
1987 RESEARCH ANALYST 3000
1987 RESEARCH 4100
1987 4100
29025
- GROUP BY ROLLUP(A, (B, C))
SQL> select
to_char(e.hiredate, 'YYYY') hireyear, d.dname,
e.job, sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by rollup(to_char(e.hiredate, 'YYYY'), (d.dname, e.job))
order by 1, 2, 4, 3;
HIREYEAR DNAME JOB SUM_SAL
---------- -------------- --------- ----------
1980 RESEARCH CLERK 800
1980 800
1981 ACCOUNTING MANAGER 2450
1981 ACCOUNTING PRESIDENT 5000
1981 RESEARCH MANAGER 2975
1981 RESEARCH ANALYST 3000
1981 SALES CLERK 950
1981 SALES MANAGER 2850
1981 SALES SALESMAN 5600
1981 22825
1982 ACCOUNTING CLERK 1300
1982 1300
1987 RESEARCH CLERK 1100
1987 RESEARCH ANALYST 3000
1987 4100
29025
连接分组
连接分组允许在 GROUP BY 之后出现多个 ROLLUP、CUBE、GROUPING SETS 操作，这样分组级别更多，报表更加精细。
不管是同类型的连接分组还是不同类型的连接分组之间，最后的分组级别种类都是每个扩展分组级别种类的乘积，分组级别是笛卡尔积。
| 序号 | 分组级别 | SQL 解释 |
| 1 | GROUP BY ROLLUP(A, B), ROLLUP(C) | GROUP BY A, B, C |
| 2 | GROUP BY A, B | |
| 3 | GROUP BY A, C | |
| 4 | GROUP BY A | |
| 5 | GROUP BY C | |
| 6 | GROUP BY NULL | |
| 7 | GROUP BY ROLLUP(A, B), GROUPING SETS(C) | GROUP BY A, B, C |
| 8 | GROUP BY A, C | |
| 9 | GROUP BY C |
- GROUP BY ROLLUP(A, B), ROLLUP(C)
ROLLUP(A, B) 分组方式有 3 种，ROLLUP(C) 分组方式有 2 种，所以最终产生的分组方式共有 3 * 2 = 6 种。
SQL> select
d.dname, e.job,
to_char(e.hiredate, 'YYYY') hireyear, sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by rollup(d.dname, e.job), rollup(to_char(e.hiredate, 'YYYY'))
order by 1, 2, 4, 3;
DNAME JOB HIREYEAR SUM_SAL
-------------- --------- ---------- ----------
ACCOUNTING CLERK 1982 1300
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 1981 2450
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 1981 5000
ACCOUNTING PRESIDENT 5000
ACCOUNTING 1982 1300
ACCOUNTING 1981 7450
ACCOUNTING 8750
RESEARCH ANALYST 1981 3000
RESEARCH ANALYST 1987 3000
RESEARCH ANALYST 6000
RESEARCH CLERK 1980 800
RESEARCH CLERK 1987 1100
RESEARCH CLERK 1900
RESEARCH MANAGER 1981 2975
RESEARCH MANAGER 2975
RESEARCH 1980 800
RESEARCH 1987 4100
RESEARCH 1981 5975
RESEARCH 10875
SALES CLERK 1981 950
SALES CLERK 950
SALES MANAGER 1981 2850
SALES MANAGER 2850
SALES SALESMAN 1981 5600
SALES SALESMAN 5600
SALES 1981 9400
SALES 9400
1980 800
1982 1300
1987 4100
1981 22825
29025
- GROUP BY ROLLUP(A, B), GROUPING SETS(C)
ROLLUP(A, B) 分组方式有 3 种，GROUPING SETS(C) 分组方式有 1 种，所以最终产生的分组方式共有 3 * 1 = 3 种。
SQL> select
d.dname, e.job,
to_char(e.hiredate, 'YYYY') hireyear, sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by rollup(d.dname, e.job), grouping sets(to_char(e.hiredate, 'YYYY'))
order by 1, 2, 4, 3;
DNAME JOB HIREYEAR SUM_SAL
-------------- --------- ---------- ----------
ACCOUNTING CLERK 1982 1300
ACCOUNTING MANAGER 1981 2450
ACCOUNTING PRESIDENT 1981 5000
ACCOUNTING 1982 1300
ACCOUNTING 1981 7450
RESEARCH ANALYST 1981 3000
RESEARCH ANALYST 1987 3000
RESEARCH CLERK 1980 800
RESEARCH CLERK 1987 1100
RESEARCH MANAGER 1981 2975
RESEARCH 1980 800
RESEARCH 1987 4100
RESEARCH 1981 5975
SALES CLERK 1981 950
SALES MANAGER 1981 2850
SALES SALESMAN 1981 5600
SALES 1981 9400
1980 800
1982 1300
1987 4100
1981 22825
重复列分组
重复列分组也就是 GROUP BY 后面允许重复列。
SQL> select d.dname, e.job, sum(e.sal) sum_sal from dept d, emp e where d.deptno = e.deptno group by d.dname, rollup(d.dname, e.job); DNAME JOB SUM_SAL -------------- --------- ---------- SALES CLERK 950 SALES MANAGER 2850 SALES SALESMAN 5600 RESEARCH CLERK 1900 RESEARCH ANALYST 6000 RESEARCH MANAGER 2975 ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 SALES 9400 RESEARCH 10875 ACCOUNTING 8750 SALES 9400 RESEARCH 10875 ACCOUNTING 8750
扩展分组函数
GROUPING、GROUPING_ID 和 GROUP_ID 三个函数在生成有意义的报表、结果行过滤、排序中有很重要的作用。
- 使用
GROUPING函数制作有意义的报表，以及对结果行进行过滤 - 使用
GROUPING_ID函数对结果行进行过滤和排序 - 使用
GROUP_ID函数剔除重复行
GROUPING 函数
对扩展 GROUP BY 子句来说，比如 ROLLUP、CUBE 会生成标准分组、一系列小计及合计，这样查询结果中，有些行的列值就会存在 NULL。NULL 在扩展 GROUP BY 子句中有特殊意义，结果行中的列值为 NULL，一般就意味着是对此列的小计或合计，但是 NULL 也有可能是原始数据存在的 NULL，如 EMP 表中的 MGR 字段就存在 NULL 值，所以引入了 GROUPING 函数专门处理扩展 GROUP BY 子句分组结果中 NULL 值的问题：
- 它只接受一个参数，且此参数来自
ROLLUP、CUBE、GROUPING SETS中的列，当然也可以来自GROUP BY中但不在上述三个子句中的列（包括简单GROUP BY也可以使用此函数，但是结果肯定是0） GROUPING函数对于是小计或合计的列返回1，否则返回0。如果小计或合计列的值是NULL，但是原始数据可能也存在NULL，则常使用GROUPING函数来区分最终结果行中的NULL是原始数据中存在的，还是小计列或合计列的值，常和DECODE函数配合使用。
如果要制作有意义的扩展 GROUP BY 报表，那么首先必须区分哪些列是小计、合计，哪些列不是：
SQL> select
d.dname, e.mgr,
sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by rollup(d.dname, e.mgr)
order by 1, 3, 2;
DNAME MGR SUM_SAL
-------------- ---------- ----------
ACCOUNTING 7782 1300
ACCOUNTING 7839 2450
ACCOUNTING 5000
ACCOUNTING 8750
RESEARCH 7902 800
RESEARCH 7788 1100
RESEARCH 7839 2975
RESEARCH 7566 6000
RESEARCH 10875
SALES 7839 2850
SALES 7698 6550
SALES 9400
29025
上述 SQL 结果集中， 第三行和第四行 MGR 列都为 NULL，那么到底哪个列是小计呢？
可以通过 GROUPING 函数来进行区分：
SQL> select
d.dname, e.mgr,
grouping(e.mgr) flag,
sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by rollup(d.dname, e.mgr)
order by 1, 3, 2;
DNAME MGR FLAG SUM_SAL
-------------- ---------- ---------- ----------
ACCOUNTING 7782 0 1300
ACCOUNTING 7839 0 2450
ACCOUNTING 0 5000
ACCOUNTING 1 8750
RESEARCH 7566 0 6000
RESEARCH 7788 0 1100
RESEARCH 7839 0 2975
RESEARCH 7902 0 800
RESEARCH 1 10875
SALES 7698 0 6550
SALES 7839 0 2850
SALES 1 9400
1 29025
上述 SQL 结果中， FLAG 字段值为1的行表示小计或合计，FLAG 字段值为0的行表示标准分组。
GROUPING_ID 函数
GROUPING_ID 函数也可以实现过滤分组级别，另一个很重要的功能就是排序结果，只有结果有一定顺序才能使报表的可读性更强，从而反映多维数据分析的作用，不管 ROLLUP、CUBE、GROUPING SETS 的结果是否有默认排序，都是不可靠的，可靠的只有显示排序。
GROUPING_ID 函数可以接受多个参数，这些参数来自于 ROLLUP、CUBE、GROUPING SETS 中的列，按列从左至右顺序计算，如果此列是标准分组列则为0，如果此列是小计或合计则为1，然后按列顺序将计算结果组成二进制序列（位向量），最后再将位向量转为十进制数。
一般使用 GROUPING_ID 函数，列的顺序要与 ROLLUP、CUBE、GROUPING SETS 中列的顺序保持一致，这样便于分析。
| 序号 | 分组级别 | SQL 解释 | 位向量 | GROUPING_ID 结果 |
| 1 | GROUP BY ROLLUP(A, B, C) | GROUP BY A, B, C | 0 0 0 | 0 |
| 2 | GROUP BY A, B | 0 0 1 | 1 | |
| 3 | GROUP BY A | 0 1 1 | 3 | |
| 4 | GROUP BY NULL | 1 1 1 | 7 | |
| 5 | GROUP BY CUBE(A, B) | GROUP BY A, B | 0 0 | 0 |
| 6 | GROUP BY A, B | 0 1 | 1 | |
| 7 | GROUP BY B | 1 0 | 2 | |
| 8 | GROUP BY NULL | 1 1 | 3 |
- GROUP BY ROLLUP(A, B, C)
SQL> select
to_char(e.hiredate, 'YYYY') hireyear, d.dname, e.job,
grouping_id(to_char(e.hiredate, 'YYYY'), d.dname, e.job) flag,
sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by rollup(to_char(e.hiredate, 'YYYY'), d.dname, e.job)
--having grouping_id(to_char(e.hiredate, 'YYYY'), d.dname, e.job) = 0
order by grouping_id(to_char(e.hiredate, 'YYYY'), d.dname, e.job)
;
HIREYEAR DNAME JOB FLAG SUM_SAL
---------- -------------- --------- ---------- ----------
1982 ACCOUNTING CLERK 0 1300
1987 RESEARCH CLERK 0 1100
1981 ACCOUNTING PRESIDENT 0 5000
1981 SALES CLERK 0 950
1981 SALES MANAGER 0 2850
1981 ACCOUNTING MANAGER 0 2450
1980 RESEARCH CLERK 0 800
1981 RESEARCH ANALYST 0 3000
1981 RESEARCH MANAGER 0 2975
1987 RESEARCH ANALYST 0 3000
1981 SALES SALESMAN 0 5600
1981 ACCOUNTING 1 7450
1981 RESEARCH 1 5975
1981 SALES 1 9400
1980 RESEARCH 1 800
1987 RESEARCH 1 4100
1982 ACCOUNTING 1 1300
1980 3 800
1982 3 1300
1987 3 4100
1981 3 22825
7 29025
- GROUP BY CUBE(A, B)
SQL> select
to_char(e.hiredate, 'YYYY') hireyear, d.dname,
grouping_id(to_char(e.hiredate, 'YYYY'), d.dname) flag,
sum(e.sal) sum_sal
from dept d, emp e
where d.deptno = e.deptno
group by cube(to_char(e.hiredate, 'YYYY'), d.dname)
--having grouping_id(to_char(e.hiredate, 'YYYY'), d.dname) = 0
order by grouping_id(to_char(e.hiredate, 'YYYY'), d.dname)
;
HIREYEAR DNAME FLAG SUM_SAL
---------- -------------- ---------- ----------
1987 RESEARCH 0 4100
1981 ACCOUNTING 0 7450
1981 RESEARCH 0 5975
1981 SALES 0 9400
1982 ACCOUNTING 0 1300
1980 RESEARCH 0 800
1980 1 800
1981 1 22825
1987 1 4100
1982 1 1300
SALES 2 9400
RESEARCH 2 10875
ACCOUNTING 2 8750
3 29025
GROUP_ID 函数
扩展 GROUP BY 分组允许多种复杂分组操作，如部分分组、重复列分组、连接分组等，有时候为了实现复杂的报表功能，会有重复分组统计出现，而GROUP_ID 函数就可以区分重复分组结果，第1次出现为0，以后每次出现增加1，GROUP_ID 无参数且常在 HAVING 中使用以达到过滤重复统计的目的。
SQL> select
d.dname, e.job,
sum(e.sal) sum_sal,
group_id() gi
from dept d, emp e
where d.deptno = e.deptno
group by grouping sets(rollup(d.dname), rollup(e.job))
--having group_id() = 0
;
DNAME JOB SUM_SAL GI
-------------- --------- ---------- ----------
CLERK 4150 0
SALESMAN 5600 0
PRESIDENT 5000 0
MANAGER 8275 0
ANALYST 6000 0
ACCOUNTING 8750 0
RESEARCH 10875 0
SALES 9400 0
29025 0
29025 1
扩展 GROUP BY 实例
- 按规则生成报表，并且排序
创建数据表：
SQL> create table books( d_order_date date, --订购日期 n_order_no number, --订单号 vc_order_book varchar2(10), --订购书籍 n_order_fee number, --订单总金额 n_order_num number --订单明细数目 ); 表已创建。
写入数据：
SQL> insert into books select date'2010-05-01' + level, trunc(dbms_random.value * 10000), 'book1', 100 * level, level from dual connect by level <= 5; 已创建 5 行。 SQL> insert into books select date'2010-06-01' + level, trunc(dbms_random.value * 10000), 'book2', 100 * level, level from dual connect by level <= 5; 已创建 5 行。 SQL> COMMIT;
要求在每组 VC_ORDER_BOOK 内，按日期升序排列，标准分组在前，小计在后，合计最后：
SQL> select
decode(grouping_id(b.vc_order_book, b.d_order_date, b.n_order_no),
3, b.vc_order_book || ' 小计',
7, '合计',
d_order_date
) d_order_date,
--grouping_id(b.vc_order_book, b.d_order_date, b.n_order_no) flag,
b.n_order_no,
decode(grouping_id(b.vc_order_book, b.d_order_date, b.n_order_no),
3, null,
b.vc_order_book
) vc_order_book,
sum(b.n_order_fee) n_order_fee,
sum(b.n_order_num) n_order_num
from books b
group by rollup(b.vc_order_book, (b.d_order_date, b.n_order_no))
order by b.vc_order_book, b.d_order_date;
D_ORDER_DATE N_ORDER_NO VC_ORDER_B N_ORDER_FEE N_ORDER_NUM
--------------- ---------- ---------- ----------- -----------
2010-05-02 2044 book1 100 1
2010-05-03 2075 book1 200 2
2010-05-04 3987 book1 300 3
2010-05-05 2698 book1 400 4
2010-05-06 3538 book1 500 5
book1 小计 1500 15
2010-06-02 7920 book2 100 1
2010-06-03 1375 book2 200 2
2010-06-04 8450 book2 300 3
2010-06-05 5084 book2 400 4
2010-06-06 7389 book2 500 5
book2 小计 1500 15
合计 3000 30
- 实现类型 SQL*PLUS 的 BREAK 报表功能
BREAK 的作用就是能够替换重复出现的单元格为空格，这样做明细报表的时候就很有作用，对同一个大类，只需要第一次出现的时候单元格值保留即可。
SQL> break on dname;
SQL> select
decode(grouping_id(d.dname, e.job),
1, d.dname || ' 小计',
3, '合计',
d.dname
) dname,
--grouping_id(d.dname, e.job) flag,
e.job,
sum(e.sal) sum_sal
from emp e, dept d
where e.deptno = d.deptno
group by rollup(d.dname, e.job)
order by 1, 2;
DNAME JOB SUM_SAL
------------------- --------- ----------
ACCOUNTING CLERK 1300
MANAGER 2450
PRESIDENT 5000
ACCOUNTING 小计 8750
RESEARCH ANALYST 6000
CLERK 1900
MANAGER 2975
RESEARCH 小计 10875
SALES CLERK 950
MANAGER 2850
SALESMAN 5600
SALES 小计 9400
合计 29025
参考资料
《剑破冰山 —— Oracle 开发艺术》
原创文章，转载请注明出处：http://www.opcoder.cn/article/21/