Oracle的group by除了基本用法以外，还有3种扩展用法，分别是rollup、cube、grouping sets。

1rollup

假设有一个表test，有A、B、C、D、E5列。

如果使用group by rollup(A,B,C)，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。roll up的意思是“卷起”，这也可以帮助我们理解group by rollup就是对选择的列从右到左以一次少一列的方式进行grouping直到所有列都去掉后的grouping(也就是全表grouping)，对于n个参数的rollup，有n+1次的grouping。以下2个sql的结果集是一样的：

Select A,C,sum(E) from test group by rollup(A,C)

与

union all

Select null,sum(E) from test

2cube

cube的意思是立方，对cube的每个参数，都可以理解为取值为参与grouping和不参与grouping两个值的一个维度，然后所有维度取值组合的集合就是grouping的集合，对于n个参数的cube，有2^n次的grouping。如果使用group by cube(A,C),，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作，一共是2^3=8次grouping。同rollup一样，也可以用基本的group by加上结果集的union all写出一个与group by cube结果集相同的sql：

3grouping sets

grouping sets就是对参数中的每个参数做grouping，也就是有几个参数做几次grouping,例如使用group by grouping sets(A,C)，则对(A),(B),(C)进行group by，如果使用group by grouping sets((A,B),则对(A,(C)进行group by。甚至grouping by grouping set(A,A)都是语法允许的，也就是对(A)进行2次group by,grouping sets的参数允许重复

4总结

rollup(N+1个分组方案)

cube(2^N个分组方案)

grouping sets (自定义罗列出分组方案)

5注意点

5.1机制不同

在rollup和cube的说明中分别给出了用基本group by加结果集union all给出了结果集相同的sql，但这只是为了理解的方便而给出的sql，并不说明rollup和cube与基本group by加结果集union all等价。实际上两者的内部机制是安全不一样的，前者除了写法简洁以外，运行时不需多次扫描表，效率远比后者高。

5.2集合可运算

3种扩展用法的参数可以是源表中的某一个具体的列，也可以是若干列经过计算而形成的一个新列（比如说A+B，A||B），也可以是这两种列的一个集合（例如（A+B，C）），对于grouping set更是特殊，可以是空集合()，表示对全表进行group by。

5.3group by与rollup,cube组合使用

3）Group by的基本用法以及这3种扩展用法可以组合使用，也就是说可以出现group by A,rollup(A,B)这样的用法，oracle将对出现在group by中的每种用法的grouping列集合做笛卡尔积然后对其中的每一个元素做group by。这话说起来挺绕口，举例说明吧，group by A,B)，基本用法的grouping集合是(A),B)的grouping集合是((A,(A),()),两个集合的笛卡尔积集合是((A,A,(A,A),(A))，所以会首先对(A,B)做group by，然后对(A,A)做group by，最后对(A)做group by。实际上对(A,B)做group by和对(A,B)做group by两者是完全等价的(group by A,B结果和group by A,B完全一样)，同理对(A,A)做group by和对(A)做group by也是等价的。简化后的结果就是首先对(A,B)做group by，然后对(A)做group by，最后再对(A)做group by。下面给出两个等价的sql以便理解：

Union all

6grouping()、grouping_id()、group_id()

6.1grouping()

参数只有一个，而且必须为group by中出现的某一列，表示结果集的一行是否对该列做了grouping。对于对该列做了grouping的行而言，grouping()=0，反之为1；

6.2grouping_id()

参数可以是多个，但必须为group by中出现的列。Grouping_id()的返回值其实就是参数中的每列的grouping()值的二进制向量，例如如果grouping(A)=1，grouping(B)=0，则grouping_id(A,B)的返回值就是二进制的10，转成10进制就是2。

6.3group_id()

无参数。见上面的说明3），group by对某些列的集合会进行重复的grouping，而实际上绝大多数情况下对结果集中的这些重复行是不需要的，那就必须有办法剔出这些重复grouping的行。当结果集中有n条重复grouping而形成的行时，每行的group_id()分别是0,1,…,n，这样我们在条件中加入一个group_id()<1就可以剔出这些重复grouping的行了。

7示例

7.1建表与数据

sql> create table test(department_id number,a varchar2(20),b varchar2(20));

Table created

sql> insert into test values(10,'A','B');

1 row inserted

sql> commit;

Commit complete

7.2查询语句

selectdepartment_id,

a,43)">b,43)">grouping(department_id),43)">grouping(a),43)">grouping(b)

fromtest

groupbyrollup(department_id,a,b)

orderby4,5,6;

groupbycube(department_id,6;