Oracle分组查询4——扩展分组函数

Oracle分组查询——扩展分组函数：grouping、grouping_id，group_id

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','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 3
b 8
总计 11

grouping中只能有一个参数

2、grouping_id

SQL> select grouping(grade),grouping(id),grouping_id(grade,id),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

SQL> select grouping(grade),grouping_id(id,grade),id);

GROUPING(GRADE) GROUPING(ID) GROUPING_ID(ID,GRADE) GRADE ID SUM(NUM)
--------------- ------------ --------------------- ----- ----- ----------
0 0 0 a 1 1
0 0 0 a 2 2
0 1 2 a 3
0 0 0 b 2 4
0 0 0 b 3 4
0 1 2 b 8
1 1 3 11

会发现grouping_id(id,grade)和grouping_id(grade,id)的值是不同的，因为GROUPING_ID()函数可以接受一列或多列，返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是将按照顺序对每一列调用GROUPING函数的结果组合起来，所以说01和10的值不一样的

GOURPING位向量计算

如下例所示

gradeid 位向量 GROUPING_ID()返回值

非空 非空 00 0

非空 空 01 1

空 非空 10 2

空 空 11 3

三个参数的显示结果

SQL> select grade,grade1,grouping_id(grade,grade1),sum(num) from a group by rollup (grade,id);

GRADE GRADE1 ID GROUPING_ID(GRADE,GRADE1) SUM(NUM)
----- ------ ----- ------------------------- ----------
a a1 1 0 1
a a1 2 0 2
a a1 0 3
a 1 3
b b1 2 0 4
b b1 0 4
b b2 3 0 4
b b2 0 4
b 1 8
3 11

10 rows selected

SQL> select grade,GRADE1,ID) SUM(NUM)
----- ------ ----- ---------------------------- ----------
a a1 1 0 1
a a1 2 0 2
a a1 1 3
a 3 3
b b1 2 0 4
b b1 1 4
b b2 3 0 4
b b2 1 4
b 3 8
7 11
grouping_id也可以理解为统计结果的级别

3、使用grouping_id

（1）使显示人性化

select grade,decode(grouping_id(grade,'小计','3',id);

GRADE DECODE(GROUPING_ID(GRADE,ID),' SUM(NUM)
----- ------------------------------ ----------
a 1 1
a 2 2
a 小计 3
b 2 4
b 3 4
b 小计 8
总计 11

（2）去除不是小计或总计的值

select grade,sum(num)
from a
group by rollup (grade,id)
having grouping_id(grade,id)>0

GRADE DECODE(GROUPING_ID(GRADE,' SUM(NUM)
----- ------------------------------ ----------
a 小计 3
b 小计 8
总计 11

4.group_id() 无参 去除重复

*** having group_id()=0

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!