SQL Server 2008中增强的汇总技巧

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

专注于为中小企业提供成都做网站、成都网站设计、成都外贸网站建设服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业景泰免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了1000+企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。

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

基本表如下：

 
 
 
   
  
  
  USE testDb2    
  
  
  GO    
  
  
     
  
  
  IF NOT OBJECT_ID('tb_Income') IS NULL   
  
  
  DROP 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   
  
  
     
  
  
  )    
  
  
     
  
  
  GO    
  
  
  INSERT [dbo].[tb_Income]    
  
  
  SELECT 1,'胡一刀',2011,2,5600    
  
  
  union ALL SELECT 1,'胡一刀',2011,1,5678    
  
  
  union ALL SELECT 1,'胡一刀',2011,3,6798    
  
  
  union ALL SELECT 2,'胡一刀',2011,4,7800    
  
  
  union ALL SELECT 2,'胡一刀',2011,5,8899    
  
  
  union ALL SELECT 3,'胡一刀',2012,8,8877    
  
  
     
  
  
  union ALL SELECT 1,'苗人凤',2011,1,3455    
  
  
  union ALL SELECT 1,'苗人凤',2011,2,4567    
  
  
  union ALL SELECT 2,'苗人凤',2011,3,5676    
  
  
  union ALL SELECT 3,'苗人凤',2011,4,5600    
  
  
  union ALL SELECT 2,'苗人凤',2011,5,6788    
  
  
  union ALL SELECT 2,'苗人凤',2012,6,5679    
  
  
  union ALL SELECT 2,'苗人凤',2012,7,6785    
  
  
     
  
  
  union ALL SELECT 2,'张无忌',2011,2,5600    
  
  
  union ALL SELECT 2,'张无忌',2011,3,2345    
  
  
  union ALL SELECT 2,'张无忌',2011,5,12000    
  
  
  union ALL SELECT 3,'张无忌',2011,4,23456    
  
  
  union ALL SELECT 3,'张无忌',2011,6,4567    
  
  
  union ALL SELECT 1,'张无忌',2012,7,6789    
  
  
  union ALL SELECT 1,'张无忌',2012,8,9998    
  
  
     
  
  
  union ALL SELECT 3,'赵半山',2011,7,6798    
  
  
  union ALL SELECT 3,'赵半山',2011,10,10000    
  
  
  union ALL SELECT 3,'赵半山',2011,9,12021    
  
  
  union ALL SELECT 2,'赵半山',2012,11,8799    
  
  
  union ALL SELECT 1,'赵半山',2012,12,10002    
  
  
     
  
  
  union ALL SELECT 3,'令狐冲',2011,8,7896    
  
  
  union ALL SELECT 3,'令狐冲',2011,9,7890    
  
  
  union ALL SELECT 2,'令狐冲',2011,10,7799    
  
  
  union ALL SELECT 2,'令狐冲',2011,11,9988    
  
  
  union ALL SELECT 2,'令狐冲',2012,9,34567    
  
  
  union ALL SELECT 3,'令狐冲',2012,12,5609    
  
  
     
  
  
  GO

数据如下：

 
 
 
   
  
  
  SELECT * FROM tb_Income    
  
  
     
  
  
  /*    
  
  
     
  
  
  TeamID PName CYear CMonth CMoney    
  
  
   胡一刀 2011 2 5600.00    
  
  
   胡一刀 2011 1 5678.00    
  
  
   胡一刀 2011 3 6798.00    
  
  
   胡一刀 2011 4 7800.00    
  
  
   胡一刀 2011 5 8899.00    
  
  
   胡一刀 2012 8 8877.00    
  
  
   苗人凤 2011 1 3455.00    
  
  
   苗人凤 2011 2 4567.00    
  
  
   苗人凤 2011 3 5676.00    
  
  
   苗人凤 2011 4 5600.00    
  
  
   苗人凤 2011 5 6788.00    
  
  
   苗人凤 2012 6 5679.00    
  
  
   苗人凤 2012 7 6785.00    
  
  
   张无忌 2011 2 5600.00    
  
  
   张无忌 2011 3 2345.00    
  
  
   张无忌 2011 5 12000.00    
  
  
   张无忌 2011 4 23456.00    
  
  
   张无忌 2011 6 4567.00    
  
  
   张无忌 2012 7 6789.00    
  
  
   张无忌 2012 8 9998.00    
  
  
   赵半山 2011 7 6798.00    
  
  
   赵半山 2011 10 10000.00    
  
  
   赵半山 2011 9 12021.00    
  
  
   赵半山 2012 11 8799.00    
  
  
   赵半山 2012 12 10002.00    
  
  
   令狐冲 2011 8 7896.00    
  
  
   令狐冲 2011 9 7890.00    
  
  
   令狐冲 2011 10 7799.00    
  
  
   令狐冲 2011 11 9988.00    
  
  
   令狐冲 2012 9 34567.00    
  
  
   令狐冲 2012 12 5609.00    
  
  
  */

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

小试牛刀，

 
 
 
   
  
  
  /*********使用CUBE汇总数据***************/    
  
  
     
  
  
  /********* 3w@live.cn 邀月***************/    
  
  
  SELECT TeamID as 小组ID,    
  
  
  SUM(CMoney) 总收入    
  
  
  FROM tb_Income    
  
  
  GROUP BY CUBE (TeamID)    
  
  
  ----ORDER BY TeamID desc

改进查询：

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

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

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

 
 
 
   
  
  
  /*********使用Grouping Sets创建自定义汇总数据***************/    
  
  
     
  
  
  /********* 3w@live.cn 邀月***************/    
  
  
     
  
  
  SELECT TeamID as 小组ID,PName as 姓名,CYear as 年份,----min(CMonth) as 月份,    
  
  
  SUM(CMoney) 总收入    
  
  
  FROM tb_Income    
  
  
  Where CMonth=2    
  
  
  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函数大显身手的机会来了。

 
 
 
   
  
  
  /*********使用Grouping标识汇总行***************/    
  
  
     
  
  
  /********* 3w@live.cn 邀月***************/    
  
  
  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_Income    
  
  
  GROUP BY CUBE (TeamID,CYear)

结果：

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

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

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

当前标题：SQL Server 2008中增强的汇总技巧
本文链接：http://www.gawzjz.com/qtweb/news39/196189.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：创新互联

猜你还喜欢下面的内容