SQL Server 2008中增强的汇总技巧

数据库 SQL Server
SQL Server 2008中对汇总有明显的增强,有点像Oracle的语法了。假定场景如下:某几位员工在不同时间参加了不同的项目,获取了相应的收入,现在需要按各种分类进行统计。

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

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

基本表如下:

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

数据如下:

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

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

小试牛刀,

  1. /*********使用CUBE汇总数据***************/  
  2.  
  3. /********* 3w@live.cn 邀月***************/  
  4. SELECT TeamID as 小组ID,  
  5. SUM(CMoney) 总收入  
  6. FROM tb_Income  
  7. GROUP BY CUBE (TeamID)  
  8. ----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. /*********使用ROLLUP汇总数据***************/  
  2.  
  3. /********* 3w@live.cn 邀月***************/  
  4.  
  5. SELECT TeamID as 小组ID,PName as 姓名,  
  6. SUM(CMoney) 总收入  
  7. FROM tb_Income  
  8. GROUP BY ROLLUP (TeamID,PName) 

 

邀月工作室

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

 

 

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

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

  1. /*********使用Grouping Sets创建自定义汇总数据***************/  
  2.  
  3. /********* 3w@live.cn 邀月***************/  
  4.  
  5. SELECT TeamID as 小组ID,PName as 姓名,CYear as 年份,----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标识汇总行(http://technet.microsoft.com/zh-cn/library/ms178544.aspx)

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

  1. /*********使用Grouping标识汇总行***************/  
  2.  
  3. /********* 3w@live.cn 邀月***************/  
  4. SELECT TeamID as 小组ID,CYear as 年份,  
  5. CASE WHEN grouping(TeamID)=0 AND grouping(CYear)=1 THEN '小组汇总' 
  6. WHEN grouping(TeamID)=1 AND grouping(CYear)=0 THEN '年份汇总' 
  7. WHEN grouping(TeamID)=1 AND grouping(CYear)=1 THEN '所有汇总' 
  8. else '正常行' END as 行类别,  
  9. SUM(CMoney) 总收入  
  10. FROM tb_Income  
  11. GROUP BY CUBE (TeamID,CYear) 

结果:

邀月工作室

其实,还有更复杂的Grouping_ID,不过,一般情况下用不到,需要的同学,请看MSDN:

http://technet.microsoft.com/zh-cn/library/bb510624.aspx

 

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

 

原文链接:http://www.cnblogs.com/downmoon/archive/2012/04/06/2433988.html

【编辑推荐】

  1. 从SQL Server删除大数据说开去
  2. MySQL浅谈MyISAM存储引擎
  3. 微软发布SQL Server 2012 RTM版本(附下载)
  4. MySQL服务端编码设置
  5. 论NoSQL的数据模型
责任编辑:林师授 来源: 邀月的博客
相关推荐

2011-08-19 10:40:27

SQL Server Merge命令

2011-08-19 10:13:34

SQL Server Values新用途

2011-08-19 11:00:54

SQL Server WaitFor命令

2011-08-19 10:24:46

SQL Server Top新用途

2009-04-16 15:44:10

可管理性SQL Server

2011-04-18 17:07:51

2009-04-16 18:15:19

动作审核审核活动SQL Server

2009-04-16 17:44:31

2010-10-14 09:32:52

SQL Server

2012-04-16 09:55:38

SQL Server

2009-06-22 10:22:57

SQL Server

2010-06-03 16:09:38

Windows Ser

2013-03-13 09:53:50

SQL Server

2009-04-16 17:03:12

报表开发工具报表制作SQL Server

2011-04-02 14:06:46

SQL Server MERGE

2009-04-16 17:59:25

细粒度权限

2009-04-16 18:07:39

2010-09-13 10:21:26

sql server2

2009-04-16 17:24:56

报表处理报表内嵌SQL Server

2009-02-16 16:10:49

安全审计安装SQL Server
点赞
收藏

51CTO技术栈公众号