分析函式 - CUBE和GROUPING SETS

兩者可以有同樣的效果：grouping sets(a,b,(a,b),()) 等同於 cube(a,b)

SQL> select * from test2;

A B C D
---------- ---------- ------------ ----------
A B 1.00 E
A C 1.00 E
A D 2.00 F
A D 3.00 F
C D 3.00 F
C D 4.00 F

6 rows selected

SQL> select a,b,sum(c) from test2 group by grouping sets(a,b,(a,b),()) order by a,b
2 /

A B SUM(C)
---------- ---------- ----------
A B 1
A C 1
A D 5
A 7
C D 7
C 7
B 1
C 1
D 12
14

10 rows selected

SQL> select a,b,sum(c) from test2 group by cube(a,b) order by a,b;

A B SUM(C)
---------- ---------- ----------
A B 1
A C 1
A D 5
A 7
C D 7
C 7
B 1
C 1
D 12
14

10 rows selected

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8118583/viewspace-64864/，如需轉載，請註明出處，否則將追究法律責任。