Oracle進階查詢之GROUP BY

標籤：oracle

對於group by 的方式不再累贅，進階方式如下案例。

參考老師文章：http://blog.csdn.net/fu0208/article/details/7183258，向老師致敬，辛苦了

為了方便大家學習和測試，所有的例子都是在Oracle內建使用者Scott下建立的。所用emp表，

如果沒有此表請參考文章：http://blog.csdn.net/xiaokui_wingfly/article/details/43957003中拷貝
現在客戶的需求是統計部門中每種工作的工資總額，最後還需要統計所有人的工資總數，相信這樣的需求對大家來說還是比較簡單的，很快就能寫出SQL語句，如下：

  select * from (select deptno, job, sum(sal) from emp group by deptno, job order by deptno)union all  select null deptno, null job, sum(sal) from emp;

客戶拍了一下腦袋瓜（當成西瓜拍了），再統計每個部門的工資數吧。tnnd，我加一個union all搞定，修改後的SQL語句如下：

----統計部門中每種工作的工資總額 , 還需要統計每個部門的工資數和統計所有人的工資總數  select * from (select deptno, job, sum(sal) from emp group by deptno, job order by deptno)union all  select * from (select deptno, null job, sum(sal) from emp group by deptno order by deptno)union all  select null deptno, null job, sum(sal) from emp;

其實還有一種簡單的方式可以實現，如下函數 group by rollup(...)

select deptno, job, sum(sal) 工資 from emp group by rollup(deptno, job); 

客戶的需求就好像腎虛的人尿頻一樣（誰說客戶是上帝，上帝哪來那麼多的需求？），再統計每個工作類型的工資總額吧。tnnd，我再加一個union all再搞定，修改後的SQL語句如下：

----統計部門中每種工作的工資總額 , 還需要統計所有人的工資總數和統計每個部門的工資數,再統計每個工作類型的工資總額  select * from (select deptno, job, sum(sal) from emp  group by deptno, job order by deptno)union all  select * from (select deptno, null job, sum(sal) from emp group by deptno order by deptno)union all  select null deptno, job, sum(sal)  from emp group by jobunion all  select null deptno, null job, sum(sal) from emp;

上面的方法還可以使用簡單函數方法實現，修改sql如下

select grouping(job),deptno, job, sum(sal) from emp group by cube(deptno, job) order by deptno;  select deptno, job, sum(sal) from emp group by cube(deptno, job) order by deptno; -- 簡化上面代碼

客戶想了想說，只要統計部門工資總額和工作類型工資總額就可以了（我每天都徘徊在殺人和忍住不殺之間），我fucking減掉一個union all搞定，修改後的SQL語句如下：

-- 統計工作類型工資總額和部門工資總額  select null deptno, job, sum(sal) from emp group by jobunion all  select deptno, null job, sum(sal) from emp group by deptno;

莫慌，這裡還有一個大招，修改代碼如下

select grouping(job), grouping_id(job), grouping(deptno), deptno, job, sum(sal) from emp group by grouping sets(deptno, job);  

忙活半天總算把客戶的需求都滿足了，稍微鬆了一口氣，不過既然學到新東西，我們有必要最後總結一下。

GROUP BY ROLLUP(A,B,C)：首先對(A,B,C)進行GROUP BY，然後對(A,B)進行GROUP BY，然後是(A)進行GROUP BY， 最後對全表進行GROUP BY操作。
GROUP BY CUBE(A,B,C)：首先對(A,B,C)進行GROUP BY，然後依次對(A,B)、(A,C)、(A)、(B,C)、(B)、(C)進行GROUP BY，最後對全表進行GROUP BY操作。
GROUP BY GROUPING SETS(A,B,C)：依次對(C)、(B)、(A)進行GROUP BY。

對此三個函數詳細使用方式參考文章：http://www.2cto.com/database/201204/127014.html

Oracle進階查詢之GROUP BY