1、grouping使用
使用grouping可以判断该行是数据库中本来的行，还是有统计产生的行
SQL> select grouping(grade),grade,sum(num) from a group by rollup (grade);GROUPING(GRADE) GRADE SUM(NUM)--------------- ----- ---------- 0 a 3 0 b 8 1 11
可以看出grouping值为0时说明这个值是数据库中本来的值，为1说明是统计的结果(也可以说该列为空时是1，不为空时是0)，根据这一特性，我们可以使显示结果更加人性化
select decode(grouping(grade),'0',grade,'1','总计'),sum(num) from a group by rollup (grade);
或
select case grouping(grade) when 1 then '总计' else grade end as grade,sum(num) from a group by rollup (grade);GRADE SUM(NUM)----- ----------a 3b 8总计 11
grouping中只能有一个参数
2、grouping_id
SQL> select grouping(grade),grouping(id),grouping_id(grade,id),grade,id,sum(num) from a group by rollup (grade,id);GROUPING(GRADE) GROUPING(ID) GROUPING_ID(GRADE,ID) GRADE ID SUM(NUM)--------------- ------------ --------------------- ----- ----- ---------- 0 0 0 a 1 1 0 0 0 a 2 2 0 1 1 a 3 0 0 0 b 2 4 0 0 0 b 3 4 0 1 1 b 8 1 1 3 11
会发现grouping_id(id,grade)和grouping_id(grade,id)的值是不同的，因为GROUPING_ID()函数可以接受一列或多列，返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是将按照顺序对每一列调用GROUPING函数的结果组合起来，所以说01和10的值不一样的
GOURPING位向量计算
如下例所示
grade id 位向量 GROUPING_ID()返回值非空 非空 00 0非空 空 01 1空 非空 10 2空 空 11 3
三个参数的显示结果
SQL> select grade,grade1,id,grouping_id(grade,grade1),sum(num) from a group by rollup (grade,grade1,id);GRADE GRADE1 ID GROUPING_ID(GRADE,GRADE1) SUM(NUM)----- ------ ----- ------------------------- ----------a a1 1 0 1a a1 2 0 2a a1 0 3a 1 3b b1 2 0 4b b1 0 4b b2 3 0 4b b2 0 4b 1 8 3 1110 rows selected
SQL> select grade,grade1,id,grouping_id(grade,grade1,id),sum(num) from a group by rollup (grade,grade1,id);GRADE GRADE1 ID GROUPING_ID(GRADE,GRADE1,ID) SUM(NUM)----- ------ ----- ---------------------------- ----------a a1 1 0 1a a1 2 0 2a a1 1 3a 3 3b b1 2 0 4b b1 1 4b b2 3 0 4b b2 1 4b 3 8 7 11
grouping_id也可以理解为统计结果的级别
3、使用grouping_id
（1）使显示人性化
select grade,decode(grouping_id(grade,id),'0',id,'1','小计','3','总计'),sum(num) from a group by rollup (grade,id);GRADE DECODE(GROUPING_ID(GRADE,ID),' SUM(NUM)----- ------------------------------ ----------a 1 1a 2 2a 小计 3b 2 4b 3 4b 小计 8 总计 11
（2）去除不是小计或总计的值
select grade,decode(grouping_id(grade,id),'0',id,'1','小计','3','总计'),sum(num) from a group by rollup (grade,id)having grouping_id(grade,id)>0GRADE DECODE(GROUPING_ID(GRADE,ID),' SUM(NUM)----- ------------------------------ ----------a 小计 3b 小计 8 总计 11
4、group_id的使用
当group by子句中重复使用一个列时
SQL> select grade,id,sum(num) from a group by grade,rollup (grade,id);GRADE ID SUM(NUM)----- ----- ----------a 1 1a 2 2b 2 4b 3 4a 3b 8a 3b 8
看出后面两行的值和前面两行的只是相同的，我们可以通过group_id来去除重复值
SQL> select grade,id,group_id(),sum(num) from a group by grade,rollup (grade,id);GRADE ID GROUP_ID() SUM(NUM)----- ----- ---------- ----------a 1 0 1a 2 0 2b 2 0 4b 3 0 4a 0 3b 0 8a 1 3b 1 8
SQL> select grade,id,sum(num) from a group by grade,rollup (grade,id) having group_id()=0;GRADE ID SUM(NUM)----- ----- ----------a 1 1a 2 2b 2 4b 3 4a 3b 8
GROUP_ID()唯一标识重复组
