SQL Server 2008之 聚合函數 的增強

  SQL Server　2008中對彙總有明顯的增強，可以說基本上很像Oracle的文法了。請看下面五個例子：

基本表如下：

1. USE testDb2  
2. GO  
3. IF NOT OBJECT_ID('tb_Income') IS NULL  
4. DROP TABLE [tb_Income]  
5. CREATE TABLE [dbo].[tb_Income](  
6. [TeamID] int not null,  
7. [PName] [Nvarchar](20) NOT NULL,  
8. [CYear] Smallint NOT NULL,  
9. [CMonth] TinyInt NOT NULL,  
10. [CMoney] Decimal (10,2) Not Null  
11. )  
12. GO  
13. INSERT [dbo].[tb_Income]  
14. SELECT 1,'胡一刀',2011,2,5600  
15. union ALL SELECT 1,'胡一刀',2011,1,5678  
16. union ALL SELECT 1,'胡一刀',2011,3,6798  
17. union ALL SELECT 2,'胡一刀',2011,4,7800  
18. union ALL SELECT 2,'胡一刀',2011,5,8899  
19. union ALL SELECT 3,'胡一刀',2012,8,8877  
20. union ALL SELECT 1,'苗人鳳',2011,1,3455  
21. union ALL SELECT 1,'苗人鳳',2011,2,4567  
22. union ALL SELECT 2,'苗人鳳',2011,3,5676  
23. union ALL SELECT 3,'苗人鳳',2011,4,5600  
24. union ALL SELECT 2,'苗人鳳',2011,5,6788  
25. union ALL SELECT 2,'苗人鳳',2012,6,5679  
26. union ALL SELECT 2,'苗人鳳',2012,7,6785  
27. union ALL SELECT 2,'張無忌',2011,2,5600  
28. union ALL SELECT 2,'張無忌',2011,3,2345  
29. union ALL SELECT 2,'張無忌',2011,5,12000  
30. union ALL SELECT 3,'張無忌',2011,4,23456  
31. union ALL SELECT 3,'張無忌',2011,6,4567  
32. union ALL SELECT 1,'張無忌',2012,7,6789  
33. union ALL SELECT 1,'張無忌',2012,8,9998  
34. union ALL SELECT 3,'趙半山',2011,7,6798  
35. union ALL SELECT 3,'趙半山',2011,10,10000  
36. union ALL SELECT 3,'趙半山',2011,9,12021  
37. union ALL SELECT 2,'趙半山',2012,11,8799  
38. union ALL SELECT 1,'趙半山',2012,12,10002  
39. union ALL SELECT 3,'令狐沖',2011,8,7896  
40. union ALL SELECT 3,'令狐沖',2011,9,7890  
41. union ALL SELECT 2,'令狐沖',2011,10,7799  
42. union ALL SELECT 2,'令狐沖',2011,11,9988  
43. union ALL SELECT 2,'令狐沖',2012,9,34567  
44. union ALL SELECT 3,'令狐沖',2012,12,5609  
45. GO  

資料如下：

[sql]  view plain copy print ?

1. SELECT * FROM tb_Income  

一、使用CUBE彙總資料（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

1. SELECT TeamID as 小組ID,  
2. SUM(CMoney) 總收入  
3. FROM tb_Income  
4. GROUP BY CUBE (TeamID)  
5. ----ORDER BY TeamID desc  

增加彙總：

1. SELECT TeamID as 小組ID,PName as 姓名,  
2. SUM(CMoney) 總收入  
3. FROM tb_Income  
4. GROUP BY CUBE (TeamID,PName)  

 二、使用ROLLUP彙總資料（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

1. SELECT TeamID as 小組ID,PName as 姓名,  
2. SUM(CMoney) 總收入  
3. FROM tb_Income  
4. GROUP BY ROLLUP (TeamID,PName)  

 注意：使用Rollup與指定的聚合列的順序有關。

三、使用Grouping Sets建立自定義彙總資料（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

1. SELECT TeamID as 小組ID,PName as 姓名,CYear as 年份,----min(CMonth) as 月份,  
2. SUM(CMoney) 總收入  
3. FROM tb_Income  
4. Where CMonth=2  
5. GROUP BY grouping SETS ((TeamID),(TeamID,PName),(CYear,PName))  

四、使用Grouping辨別彙總行（http://technet.microsoft.com/zh-cn/library/ms178544.aspx）

 細心的朋友可能會注意到，如果Cube後有兩個以上的彙總列時，可能會有一些列是Null,那麼這些Null值究竟本身就是Null,還是由于聚合産生的Null呢，此時，Grouping函數大顯身手的機會來了。

1. SELECT TeamID as 小組ID,CYear as 年份,  
2. CASE WHEN grouping(TeamID)=0 AND grouping(CYear)=1 THEN '小組彙總'  
3. WHEN grouping(TeamID)=1 AND grouping(CYear)=0 THEN '年份彙總'  
4. WHEN grouping(TeamID)=1 AND grouping(CYear)=1 THEN '所有彙總'  
5. else '正常行' END as 行類别,  
6. SUM(CMoney) 總收入  
7. FROM tb_Income  
8. GROUP BY CUBE (TeamID,CYear)  

五、使用Grouping_ID辨別分組級别（http://technet.microsoft.com/zh-cn/library/bb510624.aspx）--多元資料彙總的終極利器！！！

為了更清楚地說明問題，我們需要修改一下表結構，增加一個字段－－項目所在的地點（AreaID）,如下：

1. ALTER table tb_Income   
2. add AreaID int null  
3. GO  
4. update tb_Income SET AreaID=TeamID+CMonth%5+CYear%2  
5. GO  

此時資料變成這樣：

1. SELECT * FROM tb_Income  

我們需要統計小組、地區、月份三個次元的彙總資料。

1. SELECT TeamID as 小組ID,AreaID as 地點ID,CMonth as 月份,  
2. SUM(CMoney) 總收入  
3. FROM tb_Income   
4. Where AreaID IN (3,5,6,7,8,9,2,4) AND CYear =2011  AND CMonth=2  
5. GROUP BY CUBE (TeamID,AreaID,CMonth)  
6. ----ORDER  BY TeamID,AreaID,CMonth  

統計結果：

我們注意到，由于次元從兩個變成三個，此時資料比較淩亂，即使排序也不能有效解決。幸好，我們有Grouping_ID。看下例：

1. SELECT TeamID as 小組ID,AreaID as 地點ID,CMonth as 月份,  
2. CASE grouping_ID(TeamID,AreaID,CMonth)  
3.  WHEN 1 THEN '小組/地點彙總'  
4.  WHEN 2 THEN '小組/月份彙總'  
5.  WHEN 3 THEN '小組彙總'  
6.  WHEN 4 THEN '地點/月份彙總'  
7.  WHEN 5 THEN '地點彙總'  
8.  WHEN 6 THEN '月份彙總'  
9.  WHEN 7 THEN '所有彙總'  
10.  else '正常行' END as 行類别,  
11. SUM(CMoney) 總收入  
12. FROM tb_Income   
13. Where AreaID IN (3,5,6,7,8,9,2,4) AND CYear =2011  AND CMonth=2  
14. GROUP BY CUBE (TeamID,AreaID,CMonth)  
15. ----ORDER  BY TeamID,AreaID,CMonth  

注意：代碼中新增的部分，這裡需要稍微解釋一下，Grouping_ID接受幾個輸入列，傳回二進制列清單計算的整數值，你可以把這三個次元，看作是（0,1,1）、（0,1,0）這樣類似的二進制，而Grouping_ID負責将運算結果以整數形式傳回。

效果：