分析函式 - GROUPING SETS

SELECT ,
FROM
WHERE
GROUP BY GROUPING SETS (, ... ,

SQL> SELECT * FROM TEST2;

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

6 rows selected

SQL> SELECT A,B,SUM(C) FROM TEST2 group by GROUPing sets((A,B),a,b,());

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

10 rows selected

等同於：

SELECT a, b, SUM( c ) FROM test2 GROUP BY a, b UNION
SELECT a, null, SUM( c ) FROM test2 GROUP BY a, null UNION
SELECT null, b, SUM( c ) FROM test2 GROUP BY null, b UNION
SELECT null, null, SUM( c ) FROM test2

注意：在grouping sets中列A和B至少要在語句中出現一次，否則要報錯：

SELECT A,B,SUM(C) FROM TEST2 group by GROUPing sets((A),a,())

ORA-00979: 不是 GROUP BY 表示式

SQL> SELECT A,B,SUM(C) FROM TEST2 group by GROUPing sets((A),b,());

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

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