rollup只有第一个参数起作用也是理解不正确的,先看看例子吧:
SQL> select grade,id,num from a;
GRADE ID NUM
---------- ---------- ----------
a 1 1
a 2 2
b 3 4
b 4 4
对grade字段进行rollup:
SQL> select grade,sum(num) from a group by rollup(grade);
GRADE SUM(NUM)
---------- ----------
a 3
b 8
11
同时对grade和id字段进行rollup
SQL> SELECT decode(grouping_id(grade,ID),2,'小计',3,'合计',grade) grade,
2 decode(grouping_id(grade,ID),1,'小计',3,'合计',ID) ID,
3 SUM(num)
4 FROM a GROUP BY ROLLUP(grade,ID)
5 /
GRADE ID SUM(NUM)
---------- ---------- ----------
a 1 1
a 2 2
a 小计 3
b 3 4
b 4 4
b 小计 8
合计 合计 11
7 rows selected
再看看先对grade分组,再对id进行rollup的情况:
SQL> SELECT grade,
2 decode(GROUPING(ID),1,'合计',ID) ID,
3 SUM(num)
4 FROM a GROUP BY grade,rollup(ID)
5 /
GRADE ID SUM(NUM)
---------- ---------- ----------
a 1 1
a 2 2
a 合计 3
b 3 4
b 4 4
b 合计 8