经常写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 rollup| | 14 | 756 | 8 (25)| 00:00:01 | |* 2 | hash join | | 14 | 756 | 7 (15)| 00:00:01 | | 3 | table access full | dept | 4 | 88 | 3 (0)| 00:00:01 | | 4 | table access full | emp | 14 | 448 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------
可以发现，这种方法不但sql书写方便，性能也能得到提高。
这时候，如果又有人跑过来说：除了以上数据，他还需要每个职位总的业绩，你只要把rollup换成cube就可以了，如下所示：
-- cube分组 select a.dname,b.job, sum(b.sal) sum_sal from dept a,emp b where a.deptno = b.deptno group by cube(a.dname,b.job); dname job sum_sal -------------- --------- ---------- 29025 clerk 4150 analyst 6000 manager 8275 salesman 5600 president 5000 sales 9400 sales clerk 950 sales manager 2850 sales salesman 5600 research 10875 research clerk 1900 research analyst 6000 research manager 2975 accounting 8750 accounting clerk 1300 accounting manager 2450 accounting president 5000
从上面可以看出：cube比rollup的展现的粒度更细一些。
这时候，如果又有人跑过来说：他不需要那么细的数据，只需要汇总的数据，可以使用grouping sets：
需要了解更多数据库技术:GroupBy语句的扩展-Rollup、Cube和GroupingSets，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
---grouping sets分组 select to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job, sum(sal) sum_sal from dept a,emp b where a.deptno = b.deptno group by grouping sets(to_char(b.hiredate,'yyyy'),a.dname,b.job); hire dname job sum_sal ---- -------------- --------- ---------- 1987 4100 1980 800 1982 1300 1981 22825 accounting 8750 research 10875 sales 9400 clerk 4150 salesman 5600 president 5000 manager 8275 analyst 6000
本文来自网络收集，不代表计算机技术网立场，如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址：https://www.ctvol.com/dtteaching/798239.html