[转]详解Oracle高级分组函数(ROLLUP, CUBE, GROUPING SETS)
原文地址:http://blog.csdn.net/u014558001/article/details/42387929
本文主要讲解 ROLLUP, CUBE, GROUPING SETS的主要用法，这些函数可以理解为GroupBy分组函数封装后的精简用法，相当于多个union all 的组合显示效果，但是要比 多个union all的效率要高。
其实这些函数在时间的程序开发中应用的并不多，至少在我工作的多年时间中没用过几次，因为现在的各种开发工具/平台都自带了这些高级分组统计功能，使用的方便性及美观性都比这些要好。但如果临时查下数据，用这些函数还是不错的。
view plain copy
- createtable EMP2
- (
- ID NUMBER, -- 员工编号
- NAME VARCHAR2(20), --姓名
- SEX VARCHAR2(2), --性别
- HIREDATE DATE, --入职日期
- BASE VARCHAR2(20), --工作母地
- DEPT VARCHAR2(20), --所在部门
- SAL NUMBER --月工资
- );
2. 插入测试数据
[sql] view plain copy
- insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
- values (107, '小月', '女', to_date('01-09-2013', 'dd-mm-yyyy'), '北京','营运', 9000);
- insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
- values (108, '小美', '女', to_date('01-06-2011', 'dd-mm-yyyy'), '上海','营运', 11000);
- insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
- values (101, '张三', '男', to_date('01-01-2011', 'dd-mm-yyyy'), '北京','财务', 8000);
- insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
- values (102, '李四', '男', to_date('01-01-2012', 'dd-mm-yyyy'), '北京','营运', 15000);
- insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
- values (103, '王五', '男', to_date('01-01-2013', 'dd-mm-yyyy'), '上海','营运', 6000);
- insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
- values (104, '赵六', '男', to_date('01-01-2014', 'dd-mm-yyyy'), '上海','财务', 10000);
- insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
- values (105, '小花', '女', to_date('01-08-2014', 'dd-mm-yyyy'), '上海','财务', 4000);
- insert into emp2 (ID, NAME, SEX, HIREDATE,BASE, DEPT, SAL)
- values (106, '小静', '女', to_date('01-01-2015', 'dd-mm-yyyy'), '北京','财务', 6000);
- commit;
3. 查看一下刚才插入的数据
[sql] view plain copy
- select * from emp2;
4. 先看下普通分组的效果
按照地区统计每个部门的总工资
[sql] view plain copy- select base,dept ,sum(sal) from emp2
- group by base,dept;
查看结果如下：
view plain copy
- select base,dept,sum(sal) from emp2
- groupbyrollup(base,dept);
结果相当于
[sql] view plain copy
- select base,dept,sum(sal) from emp2
- group by base,dept
- unionall
- select base,null,sum(sal) from emp2
- group by base,null
- unionall
- selectnull,null,sum(sal) from emp2
- group by null,null
- order by 1,2
如果颠倒下rollup顺序则结果如下：
[sql] view plain copy- select base,dept,sum(sal) from emp2
- group by rollup(dept,base);
如果在实际查询中，有的小计或合计我们不需要，那么就要使用局部rollup,局部rollup就是将需要固定统计的列放在group by中，而不是放在rollup中。
[sql] view plain copy
- select base,dept,sum(sal) from emp2
- group by dept,rollup(base);
与group by rollup(dept,base)相比：去掉了最后一行的汇总，因为每次汇总要么是dept,base，要么是dept,null ，dept是固定的。
如果只希望看到合计则可以这样写：
[sql] view plain copy
- select base,dept ,sum(sal) from emp2
- group by rollup((base,dept));
view plain copy
- select base,dept,sum(sal) from emp2
- group by cube(base,dept)
- order by 1,2;
部分CUBE和部分ROLLUP类似，把需要固定统计的列放到group by中，不放到cube中就可以了。
如果cube中只有一个列，那么和rollup的结果一致
[sql] view plain copy
- select base,dept,sum(sal) from emp2
- group by dept,cube(base)
- order by1,2;
rollup和cube区别：
如果是ROLLUP(A,B, C)的话，GROUP BY顺序
(A、B、C)
(A、B)
(A)
最后对全表进行GROUPBY操作。
如果是GROUP BY CUBE(A, B, C)，GROUP BY顺序
(A、B、C)
(A、B)
(A、C)
(A)，
(B、C)
(B)
(C)，
最后对全表进行GROUPBY操作。
view plain copy
- select base,dept,sum(sal) from emp2
- group by grouping sets(base,dept);
结果为：
等价于
[sql] view plain copy
- select base,null,sum(sal) from emp2
- group by base,null
- unionall
- select null,dept,sum(sal) from emp2
- group by null,dept;
理解了groupingsets的原理我们用他实现rollup的功能也是可以的：
[sql] view plain copy
- select base,dept,sum(sal) from emp2
- group by grouping sets ((base,dept),dept,null);
效果如下：
