经常写SQL语句的人应该知道Group by语句的主要用法是进行分类汇总，下面是一种它最常见的用法（根据部门、职位分别统计业绩）：
SELECT a.dname,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname,b.job;
DNAME JOB SUM_SAL
-------------- --------- ----------
SALES MANAGER 2850
SALES CLERK 950
SALES SALESMAN 5600
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING CLERK 1300
RESEARCH MANAGER 2975
RESEARCH ANALYST 6000
RESEARCH CLERK 1900
这时候，如果有人跑过来跟你说：我除了以上数据之外，还要每个部门总的业绩以及所有部门加起来的业绩，这时候你很可能会想到如下的笨方法（union all）：
select * from (
SELECT a.dname,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname,b.job
UNION ALL
--实现了部门的小计
SELECT a.dname,NULL, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY a.dname
UNION ALL
--实现了所有部门总的合计
SELECT NULL,NULL, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno)
order by dname;
DNAME JOB SUM_SAL
-------------- --------- ----------
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 8750
RESEARCH CLERK 1900
RESEARCH MANAGER 2975
RESEARCH ANALYST 6000
RESEARCH 10875
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
29025
union all 合并笨办法产生的执行计划
-------------------------------------------------------------------------------
Plan hash value: 2979078843
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29 | 812 | 23 (22)| 00:00:01 |
| 1 | SORT ORDER BY | | 29 | 812 | 23 (22)| 00:00:01 |
| 2 | VIEW | | 29 | 812 | 22 (19)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | HASH GROUP BY | | 14 | 756 | 8 (25)| 00:00:01 |
|* 5 | HASH JOIN | | 14 | 756 | 7 (15)| 00:00:01 |
| 6 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
| 8 | HASH GROUP BY | | 14 | 672 | 8 (25)| 00:00:01 |
|* 9 | HASH JOIN | | 14 | 672 | 7 (15)| 00:00:01 |
| 10 | TABLE ACCESS FULL| DEPT | 4 | 88 | 3 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 |
| 12 | SORT AGGREGATE | | 1 | 39 | | |
|* 13 | HASH JOIN | | 14 | 546 | 7 (15)| 00:00:01 |
| 14 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------- 其实，如果你知道Group By的Rollup扩展的话，这种需求只是小case：
SELECT a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname,b.job);
DNAME JOB SUM_SAL
-------------- --------- ----------
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
RESEARCH CLERK 1900
RESEARCH ANALYST 6000
RESEARCH MANAGER 2975
RESEARCH 10875
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 8750
29025
rollup写法产生的执行计划
-----------------------------------------------------------------------------
Plan hash value: 1037965942
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 756 | 8 (25)| 00:00:01 |
| 1 | SORT GROUP BY R