分析函数 - 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8118583/viewspace-64862/