概述

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

1、group by的使用

--根據(jù)DEPTNO和JOB進(jìn)行分組。求相同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()--可以使用一個(gè)或者多個(gè)參數(shù)。意思是從右向左進(jìn)行數(shù)據(jù)的匯總統(tǒng)計(jì)，并生成一行，rollup是個(gè)統(tǒng)計(jì)函數(shù)。

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

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

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

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

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

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

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

首先根據(jù)JOB(對(duì)所有的JOB進(jìn)行匯總)，匯總出4，8，12行，在根據(jù)E.DEPTNO(對(duì)所有的DEPTNO進(jìn)行匯總),匯總出第十三行數(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查詢(xún)出前九條數(shù)據(jù)，其次對(duì)E.DEPTNO進(jìn)行匯總，但是必須考慮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會(huì)對(duì)條件中的每一個(gè)條件進(jìn)行單獨(dú)的匯總：即對(duì)單獨(dú)的列進(jìn)行匯總

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

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

4、GROUPING 的使用

GROUPING函數(shù)可以接受一列，返回0或者1。如果列值為空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查詢(xún)中使用。當(dāng)需要在返回空值的地方顯示某個(gè)值時(shí)，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ù)進(jìn)行轉(zhuǎn)換這種不友好的顯示：

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

SELECT DECODE(GROUPING(E.DEPTNO), 1, '總計(jì)', E.DEPTNO) AS 部門(mén), 
CASE 
WHEN GROUPING(E.JOB) = 1 AND GROUPING(E.DEPTNO) = 0 THEN 
'小計(jì)' 
ELSE 
E.JOB 
END AS 工作種類(lèi), 
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);