SQL Server 2008中的汇总计算技巧

SQL Server　2008中对汇总有明显的增强，有点像Oracle的语法了。请看下面五个例子：

假定场景如下：某几位员工在不同时间参加了不同的项目，获取了相应的收入，现在需要按各种分类进行统计。

基本表如下：

[sql] view plaincopyprint?

1. USE testDb2 
2. GO 
3.  
4. IF NOT OBJECT_ID('tb_Income')ISNULL 
5. DROPTABLE [tb_Income] 
6.  
7. /****** Object: Table [dbo].[tb_Income] ScriptDate: 2012/4/5 8:19:21 ******/ 
8.  
9. CREATETABLE [dbo].[tb_Income]( 
10. [TeamID] intnot null, 
11. [PName] [Nvarchar](20) NOTNULL, 
12. [CYear] SmallintNOT NULL, 
13. [CMonth] TinyInt NOTNULL, 
14. [CMoney] Decimal (10,2)Not Null 
15.  
16. ) 
17.  
18. GO 
19. INSERT [dbo].[tb_Income] 
20. SELECT 1,'胡一刀',2011,2,5600 
21. unionALL SELECT 1,'胡一刀',2011,1,5678 
22. unionALL SELECT 1,'胡一刀',2011,3,6798 
23. unionALL SELECT 2,'胡一刀',2011,4,7800 
24. unionALL SELECT 2,'胡一刀',2011,5,8899 
25. unionALL SELECT 3,'胡一刀',2012,8,8877 
26.  
27. unionALL SELECT 1,'苗人凤',2011,1,3455 
28. unionALL SELECT 1,'苗人凤',2011,2,4567 
29. unionALL SELECT 2,'苗人凤',2011,3,5676 
30. unionALL SELECT 3,'苗人凤',2011,4,5600 
31. unionALL SELECT 2,'苗人凤',2011,5,6788 
32. unionALL SELECT 2,'苗人凤',2012,6,5679 
33. unionALL SELECT 2,'苗人凤',2012,7,6785 
34.  
35. unionALL SELECT 2,'张无忌',2011,2,5600 
36. unionALL SELECT 2,'张无忌',2011,3,2345 
37. unionALL SELECT 2,'张无忌',2011,5,12000 
38. unionALL SELECT 3,'张无忌',2011,4,23456 
39. unionALL SELECT 3,'张无忌',2011,6,4567 
40. unionALL SELECT 1,'张无忌',2012,7,6789 
41. unionALL SELECT 1,'张无忌',2012,8,9998 
42.  
43. unionALL SELECT 3,'赵半山',2011,7,6798 
44. unionALL SELECT 3,'赵半山',2011,10,10000 
45. unionALL SELECT 3,'赵半山',2011,9,12021 
46. unionALL SELECT 2,'赵半山',2012,11,8799 
47. unionALL SELECT 1,'赵半山',2012,12,10002 
48.  
49. unionALL SELECT 3,'令狐冲',2011,8,7896 
50. unionALL SELECT 3,'令狐冲',2011,9,7890 
51. unionALL SELECT 2,'令狐冲',2011,10,7799 
52. unionALL SELECT 2,'令狐冲',2011,11,9988 
53. unionALL SELECT 2,'令狐冲',2012,9,34567 
54. unionALL SELECT 3,'令狐冲',2012,12,5609 
55.  
56. GO 
57.  
58.   

USE testDb2GOIF NOT OBJECT_ID('tb_Income') IS NULLDROP TABLE [tb_Income]/****** Object: Table [dbo].[tb_Income] Script Date: 2012/4/5 8:19:21 ******/CREATE TABLE [dbo].[tb_Income]([TeamID] int not null,[PName] [Nvarchar](20) NOT NULL,[CYear] Smallint NOT NULL,[CMonth] TinyInt NOT NULL,[CMoney] Decimal (10,2) Not Null)GOINSERT [dbo].[tb_Income]SELECT 1,'胡一刀',2011,2,5600union ALL SELECT 1,'胡一刀',2011,1,5678union ALL SELECT 1,'胡一刀',2011,3,6798union ALL SELECT 2,'胡一刀',2011,4,7800union ALL SELECT 2,'胡一刀',2011,5,8899union ALL SELECT 3,'胡一刀',2012,8,8877union ALL SELECT 1,'苗人凤',2011,1,3455union ALL SELECT 1,'苗人凤',2011,2,4567union ALL SELECT 2,'苗人凤',2011,3,5676union ALL SELECT 3,'苗人凤',2011,4,5600union ALL SELECT 2,'苗人凤',2011,5,6788union ALL SELECT 2,'苗人凤',2012,6,5679union ALL SELECT 2,'苗人凤',2012,7,6785union ALL SELECT 2,'张无忌',2011,2,5600union ALL SELECT 2,'张无忌',2011,3,2345union ALL SELECT 2,'张无忌',2011,5,12000union ALL SELECT 3,'张无忌',2011,4,23456union ALL SELECT 3,'张无忌',2011,6,4567union ALL SELECT 1,'张无忌',2012,7,6789union ALL SELECT 1,'张无忌',2012,8,9998union ALL SELECT 3,'赵半山',2011,7,6798union ALL SELECT 3,'赵半山',2011,10,10000union ALL SELECT 3,'赵半山',2011,9,12021union ALL SELECT 2,'赵半山',2012,11,8799union ALL SELECT 1,'赵半山',2012,12,10002union ALL SELECT 3,'令狐冲',2011,8,7896union ALL SELECT 3,'令狐冲',2011,9,7890union ALL SELECT 2,'令狐冲',2011,10,7799union ALL SELECT 2,'令狐冲',2011,11,9988union ALL SELECT 2,'令狐冲',2012,9,34567union ALL SELECT 3,'令狐冲',2012,12,5609GO

数据如下：

[sql] view plaincopyprint?

1. SELECT *FROM tb_Income 
2.  
3. /* 
4.  
5. TeamID PName CYear CMonth CMoney 
6. 1 胡一刀 2011 2 5600.00 
7. 1 胡一刀 2011 1 5678.00 
8. 1 胡一刀 2011 3 6798.00 
9. 2 胡一刀 2011 4 7800.00 
10. 2 胡一刀 2011 5 8899.00 
11. 3 胡一刀 2012 8 8877.00 
12. 1 苗人凤 2011 1 3455.00 
13. 1 苗人凤 2011 2 4567.00 
14. 2 苗人凤 2011 3 5676.00 
15. 3 苗人凤 2011 4 5600.00 
16. 2 苗人凤 2011 5 6788.00 
17. 2 苗人凤 2012 6 5679.00 
18. 2 苗人凤 2012 7 6785.00 
19. 2 张无忌 2011 2 5600.00 
20. 2 张无忌 2011 3 2345.00 
21. 2 张无忌 2011 5 12000.00 
22. 3 张无忌 2011 4 23456.00 
23. 3 张无忌 2011 6 4567.00 
24. 1 张无忌 2012 7 6789.00 
25. 1 张无忌 2012 8 9998.00 
26. 3 赵半山 2011 7 6798.00 
27. 3 赵半山 2011 10 10000.00 
28. 3 赵半山 2011 9 12021.00 
29. 2 赵半山 2012 11 8799.00 
30. 1 赵半山 2012 12 10002.00 
31. 3 令狐冲 2011 8 7896.00 
32. 3 令狐冲 2011 9 7890.00 
33. 2 令狐冲 2011 10 7799.00 
34. 2 令狐冲 2011 11 9988.00 
35. 2 令狐冲 2012 9 34567.00 
36. 3 令狐冲 2012 12 5609.00 
37. */ 
38.  
39.   

SELECT * FROM tb_Income/*TeamID PName CYear CMonth CMoney1 胡一刀 2011 2 5600.001 胡一刀 2011 1 5678.001 胡一刀 2011 3 6798.002 胡一刀 2011 4 7800.002 胡一刀 2011 5 8899.003 胡一刀 2012 8 8877.001 苗人凤 2011 1 3455.001 苗人凤 2011 2 4567.002 苗人凤 2011 3 5676.003 苗人凤 2011 4 5600.002 苗人凤 2011 5 6788.002 苗人凤 2012 6 5679.002 苗人凤 2012 7 6785.002 张无忌 2011 2 5600.002 张无忌 2011 3 2345.002 张无忌 2011 5 12000.003 张无忌 2011 4 23456.003 张无忌 2011 6 4567.001 张无忌 2012 7 6789.001 张无忌 2012 8 9998.003 赵半山 2011 7 6798.003 赵半山 2011 10 10000.003 赵半山 2011 9 12021.002 赵半山 2012 11 8799.001 赵半山 2012 12 10002.003 令狐冲 2011 8 7896.003 令狐冲 2011 9 7890.002 令狐冲 2011 10 7799.002 令狐冲 2011 11 9988.002 令狐冲 2012 9 34567.003 令狐冲 2012 12 5609.00*/

一、使用CUBE汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

小试牛刀，

[sql] view plaincopyprint?

1. /*********使用CUBE汇总数据***************/ 
2.  
3. /********* [e-mail] 邀月***************/ 
4. SELECT TeamIDas 小组ID, 
5. SUM(CMoney) 总收入 
6. FROM tb_Income 
7. GROUPBYCUBE (TeamID) 
8. ----ORDER BY TeamID desc 

/*********使用CUBE汇总数据***************//********* [e-mail] 邀月***************/SELECT TeamID as 小组ID,SUM(CMoney) 总收入FROM tb_IncomeGROUP BY CUBE (TeamID)----ORDER BY TeamID desc

改进查询：

[sql] view plaincopyprint?

1. SELECT TeamIDas 小组ID,PNameas 姓名, 
2. SUM(CMoney) 总收入 
3. FROM tb_Income 
4. GROUPBYCUBE (TeamID,PName) 

SELECT TeamID as 小组ID,PName as 姓名,SUM(CMoney) 总收入FROM tb_IncomeGROUP BY CUBE (TeamID,PName)

二、使用ROLLUP汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

[sql] view plaincopyprint?

1. /*********使用ROLLUP汇总数据***************/ 
2.  
3. /********* [e-mail] 邀月***************/ 
4.  
5. SELECT TeamIDas 小组ID,PNameas 姓名, 
6. SUM(CMoney) 总收入 
7. FROM tb_Income 
8. GROUPBYROLLUP (TeamID,PName) 

/*********使用ROLLUP汇总数据***************//********* [e-mail] 邀月***************/SELECT TeamID as 小组ID,PName as 姓名,SUM(CMoney) 总收入FROM tb_IncomeGROUP BY ROLLUP (TeamID,PName)

注意：使用Rollup与指定的聚合列的顺序有关。

三、使用Grouping Sets创建自定义汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

除了Cube和Rollup，还有更加灵活强大的自定义集合汇总－－Grouping Sets

[delphi] view plaincopyprint?

1. /*********使用Grouping Sets创建自定义汇总数据***************/ 
2.  
3. /********* [e-mail] 邀月***************/ 
4.  
5. SELECT TeamID as 小组ID,PNameas 姓名,CYearas 年份,----min(CMonth)as 月份, 
6. SUM(CMoney) 总收入 
7. FROM tb_Income 
8. Where CMonth=2 
9. GROUP BY grouping SETS ((TeamID),(TeamID,PName),(CYear,PName)) 

/*********使用Grouping Sets创建自定义汇总数据***************//********* [e-mail] 邀月***************/SELECT TeamID as 小组ID,PName as 姓名,CYear as 年份,----min(CMonth) as 月份,SUM(CMoney) 总收入FROM tb_IncomeWhere CMonth=2GROUP BY grouping SETS ((TeamID),(TeamID,PName),(CYear,PName))

四、使用Grouping标识汇总行（http://technet.microsoft.com/zh-cn/library/ms178544.aspx）

细心的朋友可能会注意到，如果Cube后有两个以上的汇总列时，可能会有一些列是Null,那么这些Null值究竟本身就是Null,还是由于聚合产生的Null呢，此时，Grouping函数大显身手的机会来了。

[sql] view plaincopyprint?

1. /*********使用Grouping标识汇总行***************/ 
2.  
3. /********* [e-mail] 邀月***************/ 
4. SELECT TeamIDas 小组ID,CYearas 年份, 
5. CASEWHENgrouping(TeamID)=0AND grouping(CYear)=1THEN'小组汇总' 
6. WHENgrouping(TeamID)=1AND grouping(CYear)=0THEN'年份汇总' 
7. WHENgrouping(TeamID)=1AND grouping(CYear)=1THEN'所有汇总' 
8. else'正常行'ENDas 行类别, 
9. SUM(CMoney) 总收入 
10. FROM tb_Income 
11. GROUPBYCUBE (TeamID,CYear) 

/*********使用Grouping标识汇总行***************//********* [e-mail] 邀月***************/SELECT TeamID as 小组ID,CYear as 年份,CASE WHEN grouping(TeamID)=0 AND grouping(CYear)=1 THEN '小组汇总'WHEN grouping(TeamID)=1 AND grouping(CYear)=0 THEN '年份汇总'WHEN grouping(TeamID)=1 AND grouping(CYear)=1 THEN '所有汇总'else '正常行' END as 行类别,SUM(CMoney) 总收入FROM tb_IncomeGROUP BY CUBE (TeamID,CYear)

至此，如果还有美中不足的话，那就是分组还是有点凌乱，下面我们将隆重推出终极武器－－Grouping_ID，它与Grouping类似，但提供更为精细的颗粒度，以确认分组级别，当然使用也更为复杂，请看下面的示例：

五、使用Grouping_ID标识分组级别（http://technet.microsoft.com/zh-cn/library/bb510624.aspx）

为了更清楚地说明问题，我们需要修改一下表结构，增加一个字段－－项目所在的地点（AreaID）,如下：

[sql] view plaincopyprint?

1. /*************修改表结构***************************/ 
2.  
3. ALTERtable tb_Income  
4. add AreaIDintnull 
5.  
6. GO 
7.  
8. update tb_IncomeSET AreaID=TeamID+CMonth%5+CYear%2 
9. GO 

/*************修改表结构***************************/ALTER table tb_Income add AreaID int nullGOupdate tb_Income SET AreaID=TeamID+CMonth%5+CYear%2GO
此时数据变成这样：

[sql] view plaincopyprint?

1. SELECT *FROM tb_Income 
2.  
3. /* 
4. TeamID    PName    CYear    CMonth    CMoney    AreaID 
5.     胡一刀    2011    2    5600.00    4 
6.     胡一刀    2011    1    5678.00    3 
7.     胡一刀    2011    3    6798.00    5 
8.     胡一刀    2011    4    7800.00    7 
9.     胡一刀    2011    5    8899.00    3 
10.     胡一刀    2012    8    8877.00    6 
11.     苗人凤    2011    1    3455.00    3 
12.     苗人凤    2011    2    4567.00    4 
13.     苗人凤    2011    3    5676.00    6 
14.     苗人凤    2011    4    5600.00    8 
15.     苗人凤    2011    5    6788.00    3 
16.     苗人凤    2012    6    5679.00    3 
17.     苗人凤    2012    7    6785.00    4 
18.     张无忌    2011    2    5600.00    5 
19.     张无忌    2011    3    2345.00    6 
20.     张无忌    2011    5    12000.00    3 
21.     张无忌    2011    4    23456.00    8 
22.     张无忌    2011    6    4567.00    5 
23.     张无忌    2012    7    6789.00    3 
24.     张无忌    2012    8    9998.00    4 
25.     赵半山    2011    7    6798.00    6 
26.     赵半山    2011    10    10000.00    4 
27.     赵半山    2011    9    12021.00    8 
28.     赵半山    2012    11    8799.00    3 
29.     赵半山    2012    12    10002.00    3 
30.     令狐冲    2011    8    7896.00    7 
31.     令狐冲    2011    9    7890.00    8 
32.     令狐冲    2011    10    7799.00    3 
33.     令狐冲    2011    11    9988.00    4 
34.     令狐冲    2012    9    34567.00    6 
35.     令狐冲    2012    12    5609.00    5 
36. */ 

SELECT * FROM tb_Income/*TeamID PName CYear CMonth CMoney AreaID 胡一刀 2011 2 5600.00 4 胡一刀 2011 1 5678.00 3 胡一刀 2011 3 6798.00 5 胡一刀 2011 4 7800.00 7 胡一刀 2011 5 8899.00 3 胡一刀 2012 8 8877.00 6 苗人凤 2011 1 3455.00 3 苗人凤 2011 2 4567.00 4 苗人凤 2011 3 5676.00 6 苗人凤 2011 4 5600.00 8 苗人凤 2011 5 6788.00 3 苗人凤 2012 6 5679.00 3 苗人凤 2012 7 6785.00 4 张无忌 2011 2 5600.00 5 张无忌 2011 3 2345.00 6 张无忌 2011 5 12000.00 3 张无忌 2011 4 23456.00 8 张无忌 2011 6 4567.00 5 张无忌 2012 7 6789.00 3 张无忌 2012 8 9998.00 4 赵半山 2011 7 6798.00 6 赵半山 2011 10 10000.00 4 赵半山 2011 9 12021.00 8 赵半山 2012 11 8799.00 3 赵半山 2012 12 10002.00 3 令狐冲 2011 8 7896.00 7 令狐冲 2011 9 7890.00 8 令狐冲 2011 10 7799.00 3 令狐冲 2011 11 9988.00 4 令狐冲 2012 9 34567.00 6 令狐冲 2012 12 5609.00 5*/
我们需要统计小组、地区、月份三个维度的汇总数据。

[sql] view plaincopyprint?

1. /*********使用Grouping_ID标识分组级别***************/ 
2.  
3. /*********  [e-mail]  邀月***************/ 
4.  
5. SELECT TeamIDas 小组ID,AreaIDas 地点ID,CMonthas 月份, 
6. SUM(CMoney) 总收入 
7. FROM tb_Income  
8. Where AreaIDIN (3,5,6,7,8,9,2,4)AND CYear =2011 AND CMonth=2 
9. GROUPBYCUBE (TeamID,AreaID,CMonth) 
10. ----ORDER  BY TeamID,AreaID,CMonth 

/*********使用Grouping_ID标识分组级别***************//********* [e-mail] 邀月***************/SELECT TeamID as 小组ID,AreaID as 地点ID,CMonth as 月份,SUM(CMoney) 总收入FROM tb_Income Where AreaID IN (3,5,6,7,8,9,2,4) AND CYear =2011 AND CMonth=2GROUP BY CUBE (TeamID,AreaID,CMonth)----ORDER BY TeamID,AreaID,CMonth

统计结果：

我们注意到，由于维度从两个变成三个，此时数据比较凌乱，即使排序也不能有效解决。幸好，我们有Grouping_ID。看下例：

[sql] view plaincopyprint?

1. SELECT TeamIDas 小组ID,AreaIDas 地点ID,CMonthas 月份, 
2.  
3. CASE grouping_ID(TeamID,AreaID,CMonth) 
4. WHEN 1THEN'小组/地点汇总' 
5. WHEN 2THEN'小组/月份汇总' 
6. WHEN 3THEN'小组汇总' 
7. WHEN 4THEN'地点/月份汇总' 
8. WHEN 5THEN'地点汇总' 
9. WHEN 6THEN'月份汇总' 
10. WHEN 7THEN'所有汇总' 
11. else'正常行'ENDas 行类别, 
12.  
13. SUM(CMoney) 总收入 
14. FROM tb_Income  
15. Where AreaIDIN (3,5,6,7,8,9,2,4)AND CYear =2011 AND CMonth=2 
16. GROUPBYCUBE (TeamID,AreaID,CMonth) 
17. ----ORDER  BY TeamID,AreaID,CMonth 

SELECT TeamID as 小组ID,AreaID as 地点ID,CMonth as 月份,CASE grouping_ID(TeamID,AreaID,CMonth) WHEN 1 THEN '小组/地点汇总' WHEN 2 THEN '小组/月份汇总' WHEN 3 THEN '小组汇总' WHEN 4 THEN '地点/月份汇总' WHEN 5 THEN '地点汇总' WHEN 6 THEN '月份汇总' WHEN 7 THEN '所有汇总' else '正常行' END as 行类别,SUM(CMoney) 总收入FROM tb_Income Where AreaID IN (3,5,6,7,8,9,2,4) AND CYear =2011 AND CMonth=2GROUP BY CUBE (TeamID,AreaID,CMonth)----ORDER BY TeamID,AreaID,CMonth

注意：代码中新增的部分，这里需要稍微解释一下，Grouping_ID接受几个输入列，返回二进制列列表计算的整数值，你可以把这三个维度，看作是（0,1,1）、（0,1,0）这样类似的二进制，而Grouping_ID负责将运算结果以整数形式返回。

效果：

至此，Group By的汇总暂时告一段落，希望您不虚此行，有所斩获！

小结：带有Cube,Rollup,grouping Sets的Group By函数在统计与分析中有着广泛的应用，相信它的高效简捷，在特定的场合会令人你爱不释手！

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助!   [e-mail]