ROUPING SETS(c) 有3*1=3 种分 组级别。
重复列分组就是 GROUP BY 中允许重复列，比如在 ROLLUP 中使用复杂的复合列分组可能会用到， 比如 GROUP BY ROLLUP(a,(a,b))、GROUP BY a,ROLLUP(a,b) 都属于重复列。
5.1 组合列分组 组合列分组有过滤某些小计或计算一些额外的小计等功能。 前面的部分 ROLLUP、部分CUBE 都没有合计，使用组合列可以实现部分 ROLLUP、部分 CUBE的 功能，还能有合计。 需求： a. 对部门、入职时间（年）、职位进行标准分组 b. 对每个部门计算横跨入职时间（年）和职位的小计 c. 最后合计 select d.dname,to_char(e.hiredate,'yyyy') hireyear,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,(to_char(e.hiredate,'yyyy'),e.job)); CUBE 和 ROLLUP 操作都可以用组合列分组转为对应的 GROUPING SETS， 例如， ROLLUP(a,b,c) 转为等价的 GROUPING SETS 是 GROUPING SETS((a,b,c),(a,b),(a),NULL); CUBE(a,b,c) 转为等价的 GROUPING SETS 是 GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),NULL);
5.2 连接分组 连接分组是Oracle 9i 才有的功能，它允许 GROUP BY后面有多个 ROLLUP、CUBE、GROUPING SETS， 连接分组的分组级别是由每个 ROLLUP、CUBE、GROUPING SETS 分组组成的笛卡尔积。 比如 ROLLUP(a,b),ROLLUP(c,d,e) 共有分组统计级别为 3*4=12 种。 select d.dname,e.job,to_char(e.hiredate,'yyyy') hireyear,sum(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')); GROUP BY ROLLUP(d.dname,e.job),ROLLUP(to_char(e.hiredate),'yyyy') 实现了 6 种分组结果， 相当于两个 ROLLUP 的笛卡尔积，如下表：
CUBE、GROUPING SETS 都类似，利用连接分组，CUBE 可以用 ROLLUP转换： a. 当只有一列的时候，比如 ROLLUP(a) 与 CUBE(a) 是一样的，都有两种统计方式； b. 当有 n 列的时候，比如 CUBE(a,b,c) 可以转为 ROLLUP(a),ROLLUP(b),ROLLUP(c) 的连接分组表示， 也就是有 n 列的 CUBE 转为 ROLLUP 则需要拆开，转为单列 ROLLUP的连接分组即可。 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),rollup(e.job),rollup(to_char(e.hiredate,'yyyy')); <=> group by cube(d.dname,e.job,to_char(hiredate,'yyyy')); 注：连接分组一般是同类型的连接分组，不同类型的连接分组比如 GROUP BY ROLLUP...CUBE... 等是不常用的， 除非有复杂需求。
5.3 重复列分组 重复列分组也是Oracle 9i 才有的，也就是 GROUP BY 后面允许重复列。 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); <=> select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname,e.job union all select null,null,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname union all select null,null,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname;
5.4 组合列分组、连接分组、重复列分组总结 a. 组合列主要实现剔除某些不必要的小计保留合计； b. 连接分组按每个扩展分组的分组级别的笛卡尔积形式进行操作，分组类型更多更细， 比如 ROLLUP 连接分组就实现了类似 CUBE 的功能。
6. 3个扩展分组函数：GROUPING、GROUPING_ID、GROUP_ID 主要内容有： a. 使用 GROUPING 函数制作有意义的报表，以及对结果进行过滤； b. 使用 GROUPING_ID 函数对结果进行过滤及排序； c. 使用 GROUP_ID 函数剔除重复行。
6.1 GROUPING 函数 对扩展 GROUP BY 子句来说，比如 ROLLUP、CUBE 会生成标准分组、一系列小计及合计，这样查询结果中， 有些行的列值就会存在 NULL。NULL 在扩展 GROUP BY 中有特殊的意义，结果行中的列值为 NULL，一般 就意味着是此列的小计或合计，但是 NULL 也有可能是原始数据存在的 NULL(如 emp.mgr=NULL)，所以引入 了 GROUPING 函数专门处理扩展GROUP BY 分组结果中 NULL 的问题： a. 它只接受一个参数，此参数来自 ROLLUP、CUBE、GROUPING SETS 中的列； b. GROUPING 函数对于是小计或合计的列返回 1，否则返回 0。如果小计或合计列的值是 NULL，但是原始 数据可能也存在 NULL，则常使用 GROUPING 函数来区分最终结果行中的 NULL 是原始数据中存在的， 还是小计或合计列的值，常和 DECODE 函数配合使用。 6.1.1 用于格式化报表，生成有意义的报表 select d.dname,e.mgr,sum(e.sal) from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.mgr); <== 对于每个dname，计算横跨 mgr 列的小计 上述结果中第 9 行和第 11 行的 mgr 列都为 NULL，无法区分哪个列是小计，此时就可以使用 GROUPING 函数进行区分。 select d.dname,e.mgr,sum(e.sal),grouping(mgr) from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.mgr); 上述结果中第 8 行的 GROUPING(mgr)=0，第 11 行为 1，所有第 8 行的 mgr 列不是小计列，11 行才是。 下面使用DECODE + GROUPING 来制作有意义的报表： select decode(grouping(d.dname),1,'TOTAL_DEPT',d.dname) dname, decode(grouping(e.mgr),1,'SUBTOTAL_DEPT',nvl(to_char(e.mgr),'BOSS')) mgr, sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.mgr); 如果要将所有的 mgr 列小计一起放在后面显示，位置在合计之间，这种需求如何解决 （详见GROUPING_ID） 6.1.2 过滤某些分组结果 一般使用 GROUPING_ID 代替 需求：对 group by rollup(d.dname,e.mgr,e.job) 的结果保留合计和标准分组 select d.dname,e.mgr,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.mgr,e.job) having grouping(d.dname)=1 or grouping(e.job)=0;
6.2 GROUPING_ID 函数 GROUPING 函数用来生成有意义的报表及过滤一些分组级别； GROUPING_ID 函数主要用来过滤分组级别和排序结果（显示排序）。 不管 ROLLUP、CUBE、GROUPING SETS 的结果是否有默认顺序，都是不可靠的。 GROUPING_ID 函数可以接受对个参数，这些参数来自于 ROLLUP、CUBE、GROUPING SETS中的 列（参数来源和 GROUPING 函数一致），按列从左到右顺序计算，如果此列是分组列则为 0 ，如果是 对此列的小计或合计则为 1，然后按列顺序将计算结果组成二进制序列（位向量），最后将位向量转为 |