概述

今天主要分享下Oracle數(shù)據(jù)庫分組函數(shù)group by 、 rollup、cude、grouping 、grouping sets的常用用法，以下以O(shè)racle自帶schema做演示。

1、group by的使用

--根據(jù)DEPTNO和JOB進行分組。求相同DEPTNO,相同JOB的員工工資總和。

SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY E.DEPTNO,E.JOB ORDER BY E.DEPTNO; 

2、group by 配合rollup的使用

rollup()--可以使用一個或者多個參數(shù)。意思是從右向左進行數(shù)據(jù)的匯總統(tǒng)計，并生成一行，rollup是個統(tǒng)計函數(shù)。

以下是根據(jù)分組情況進行統(tǒng)計，最終進行全部匯總。

（1）簡單的使用rollup--生成一行新數(shù)據(jù)。(要生成新的一行數(shù)據(jù)，還可以使用UNION ALL)

SELECT D.DUMMY FROM DUAL D GROUP BY ROLLUP(D.DUMMY); 

（2）先根據(jù)E.DEPTNO,E.JOB進行分組，然后從右向左

SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY ROLLUP(E.DEPTNO,E.JOB) ORDER BY E.DEPTNO; 

針對以上的使用ROLLUP的結(jié)果的理解：

a：首先根據(jù)GROUP BY E.DEPTNO,E.JOB查詢出9條數(shù)據(jù)（除4，8，12，13外），在根據(jù)rollup的定義，從右向左，對ROLLUP中的參數(shù)進行小計

首先根據(jù)JOB(對所有的JOB進行匯總)，匯總出4，8，12行，在根據(jù)E.DEPTNO(對所有的DEPTNO進行匯總),匯總出第十三行數(shù)據(jù)。

(3) 特殊情況

SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY ROLLUP(E.JOB,E.DEPTNO) ORDER BY E.DEPTNO; 

理解：首先根據(jù)GROUP BY E.DEPTNO,E.JOB查詢出前九條數(shù)據(jù)，其次對E.DEPTNO進行匯總，但是必須考慮JOB，也就是相同的JOB，的所有的工資總和，所以出現(xiàn)下面五條數(shù)據(jù)。

3、group by 配合cube的使用

SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY CUBE(E.DEPTNO,E.JOB) ORDER BY E.DEPTNO; 

理解：CUBE會對條件中的每一個條件進行單獨的匯總：即對單獨的列進行匯總

GROUP BY CUBE(E.DEPTNO,E.JOB)首先根據(jù)：GROUP BY E.DEPTNO,E.JOB查詢數(shù)據(jù)，其次對E.JOB進行匯總(不考慮DEPTNO，單獨匯總，而ROLLUP是在同一個DEPTNO下面)再對E.DEPTNO進行匯總，最后全部匯總。

cube(a,b) 統(tǒng)計列包含：(a,b)、(a)、(b)、() 
cube(a,b,c) 統(tǒng)計列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、() 

4、GROUPING 的使用

GROUPING函數(shù)可以接受一列，返回0或者1。如果列值為空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查詢中使用。當(dāng)需要在返回空值的地方顯示某個值時，GROUPING()就非常有用。

SELECT GROUPING(E.DEPTNO), E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY ROLLUP(E.DEPTNO,E.JOB) ORDER BY E.DEPTNO; 

可以使用decode或者case函數(shù)進行轉(zhuǎn)換這種不友好的顯示：

SELECT  
CASE WHEN grouping(E.DEPTNO) = 1 
THEN '總計' 
ELSE E.DEPTNO || '' 
END AS 部門, 
CASE WHEN grouping(E.JOB) = 1 AND grouping(E.DEPTNO) = 0 
THEN '小計' 
ELSE E.JOB 
END AS 工作種類, 
SUM(E.SAL) FROM EMP E GROUP BY ROLLUP(E.DEPTNO,E.JOB) 
ORDER BY E.DEPTNO; 

SELECT DECODE(GROUPING(E.DEPTNO), 1, '總計', E.DEPTNO) AS 部門, 
CASE 
WHEN GROUPING(E.JOB) = 1 AND GROUPING(E.DEPTNO) = 0 THEN 
'小計' 
ELSE 
E.JOB 
END AS 工作種類, 
SUM(E.SAL) 
FROM EMP E 
GROUP BY ROLLUP(E.DEPTNO, E.JOB) 
ORDER BY E.DEPTNO; 

5、grouping sets提供了指定匯總集合條件的功能

根據(jù)E.DEPTNO,E.JOB分別匯總數(shù)據(jù)。

SELECT E.DEPTNO,E.JOB,SUM(E.SAL) FROM EMP E GROUP BY GROUPING SETS(E.DEPTNO,E.JOB);