rollup

[xu_guanghui]

我在一个地方看到一个类似这样的语句
select name, count(*) from table
group by name rollup(1)
请问这里的 rollup 是做什么用的，什么时候需要这样的条件
我试过了，如果有rollup 则出来的记录是重复的，如果没有，则不会有重复

[black_snail]

Could not find the description in my Oracle 7.3 ?

[KingSunSha]

ROLLUP is an extension to the group_by_clause that groups the
selected rows based on the values of the first n, n-1, n-2, ... 0
expressions for each row, and returns a single row of summary for
each group. You can use the ROLLUP operation to produce subtotal values.
For example, given three expressions in the ROLLUP clause of the
group_by_clause, the operation results in n+1 = 3+1 = 4 groupings.
Rows based on the values of the first ’n’ expressions are called
regular rows, and the others are called superaggregate rows.

[KingSunSha]

对于扩展统计查询ROLLUP的测试:
1.先执行一个普通的GROUP查询:
SQL> select col1,col2,count(*)
2 from t_percent
3 group by col1, col2;
COL1 COL2 COUNT(*)
---------- ---------- ----------
A 11 3
A 12 2
A 13 5
B 11 2
B 12 3
B 13 4
B 14 1
7 rows selected.

2.加上ROLLUP关键字,从而能使用函数GROUPING(),该函数获得统计分组的层次
SQL> select grouping(col1),grouping(col2),col1,col2,count(*)
2 from t_percent
3 group by rollup(col1,col2);
GROUPING(COL1) GROUPING(COL2) COL1 COL2 COUNT(*)
-------------- -------------- ---------- ---------- ----------
0 0 A 11 3
0 0 A 12 2
0 0 A 13 5
0 1 A 10
0 0 B 11 2
0 0 B 12 3
0 0 B 13 4
0 0 B 14 1
0 1 B 10
1 1 20
10 rows selected.
可以看到,在STEP1查询结构的基础上,新增加了3个输出结果,分别是COL1=A AND COL2 IS ALL/COL1=B AND COL2 IS ALL/COL1 IS ALL AND COL2 IS ALL.这三个结果是在STEP1结果上的扩展统计.

3.通过使用GROUPING()并结合DECODE()函数,我们就能获得更容易阅读的统计结果:
SQL> select decode(grouping(col1),1,'all col1', col1),
2 decode(grouping(col2),1,'all col2', col2),count(*)
3 from t_percent
4 group by rollup(col1,col2);
DECODE(GRO DECODE(GRO COUNT(*)
---------- ---------- ----------
A 11 3
A 12 2
A 13 5
A all col2 10
B 11 2
B 12 3
B 13 4
B 14 1
B all col2 10
all col1 all col2 20

10 rows selected.

[black_snail]

learn