SQL Server 2008中增强的汇总技巧

最新推荐文章于 2024-04-26 17:51:48 发布
最新推荐文章于 2024-04-26 17:51:48 发布
阅读量756 收藏
点赞数
分类专栏： Sql Server 文章标签： sql server null sql table object delphi

原文: http://blog.csdn.net/downmoon/article/details/7430645

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

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

基本表如下：

[sql] view plain copy print ?
  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. GO  
  17. INSERT [dbo].[tb_Income]  
  18. SELECT 1,'胡一刀',2011,2,5600  
  19. union ALL SELECT 1,'胡一刀',2011,1,5678  
  20. union ALL SELECT 1,'胡一刀',2011,3,6798  
  21. union ALL SELECT 2,'胡一刀',2011,4,7800  
  22. union ALL SELECT 2,'胡一刀',2011,5,8899  
  23. union ALL SELECT 3,'胡一刀',2012,8,8877  
  24.   
  25. union ALL SELECT 1,'苗人凤',2011,1,3455  
  26. union ALL SELECT 1,'苗人凤',2011,2,4567  
  27. union ALL SELECT 2,'苗人凤',2011,3,5676  
  28. union ALL SELECT 3,'苗人凤',2011,4,5600  
  29. union ALL SELECT 2,'苗人凤',2011,5,6788  
  30. union ALL SELECT 2,'苗人凤',2012,6,5679  
  31. union ALL SELECT 2,'苗人凤',2012,7,6785  
  32.   
  33. union ALL SELECT 2,'张无忌',2011,2,5600  
  34. union ALL SELECT 2,'张无忌',2011,3,2345  
  35. union ALL SELECT 2,'张无忌',2011,5,12000  
  36. union ALL SELECT 3,'张无忌',2011,4,23456  
  37. union ALL SELECT 3,'张无忌',2011,6,4567  
  38. union ALL SELECT 1,'张无忌',2012,7,6789  
  39. union ALL SELECT 1,'张无忌',2012,8,9998  
  40.   
  41. union ALL SELECT 3,'赵半山',2011,7,6798  
  42. union ALL SELECT 3,'赵半山',2011,10,10000  
  43. union ALL SELECT 3,'赵半山',2011,9,12021  
  44. union ALL SELECT 2,'赵半山',2012,11,8799  
  45. union ALL SELECT 1,'赵半山',2012,12,10002  
  46.   
  47. union ALL SELECT 3,'令狐冲',2011,8,7896  
  48. union ALL SELECT 3,'令狐冲',2011,9,7890  
  49. union ALL SELECT 2,'令狐冲',2011,10,7799  
  50. union ALL SELECT 2,'令狐冲',2011,11,9988  
  51. union ALL SELECT 2,'令狐冲',2012,9,34567  
  52. union ALL SELECT 3,'令狐冲',2012,12,5609  
  53.   
  54. GO  
  55.   
  56.    

数据如下：

[sql] view plain copy print ?
  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.    

 

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

小试牛刀，

[sql] view plain copy print ?
  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  

邀月工作室

改进查询：

[sql] view plain copy print ?
  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）

 

[sql] view plain copy print ?
  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


[delphi] view plain copy print ?
  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呢，此时，Grouping函数大显身手的机会来了。

[sql] view plain copy print ?
  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，它与Grouping类似，但提供更为精细的颗粒度，以确认分组级别，当然使用也更为复杂，请看下面的示例：

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

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

[sql] view plain copy print ?
  1. /*************修改表结构***************************/  
  2.   
  3. ALTER table tb_Income   
  4. add AreaID int null  
  5. GO  
  6. update tb_Income SET AreaID=TeamID+CMonth%5+CYear%2  
  7. GO  


此时数据变成这样：

[sql] view plain copy print ?
  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. */  


我们需要统计小组、地区、月份三个维度的汇总数据。

[sql] view plain copy print ?
  1. /*********使用Grouping_ID标识分组级别***************/  
  2.   
  3. /*********  3w@live.cn  邀月***************/  
  4.   
  5. SELECT TeamID as 小组ID,AreaID as 地点ID,CMonth as 月份,  
  6. SUM(CMoney) 总收入  
  7. FROM tb_Income   
  8. Where AreaID IN (3,5,6,7,8,9,2,4) AND CYear =2011  AND CMonth=2  
  9. GROUP BY CUBE (TeamID,AreaID,CMonth)  
  10. ----ORDER  BY TeamID,AreaID,CMonth  


统计结果：

邀月工作室

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

[sql] view plain copy print ?
  1. SELECT TeamID as 小组ID,AreaID as 地点ID,CMonth as 月份,  
  2.   
  3. CASE grouping_ID(TeamID,AreaID,CMonth)  
  4.  WHEN 1 THEN '小组/地点汇总'  
  5.  WHEN 2 THEN '小组/月份汇总'  
  6.  WHEN 3 THEN '小组汇总'  
  7.  WHEN 4 THEN '地点/月份汇总'  
  8.  WHEN 5 THEN '地点汇总'  
  9.  WHEN 6 THEN '月份汇总'  
  10.  WHEN 7 THEN '所有汇总'  
  11.  else '正常行' END as 行类别,  
  12.   
  13. SUM(CMoney) 总收入  
  14. FROM tb_Income   
  15. Where AreaID IN (3,5,6,7,8,9,2,4) AND CYear =2011  AND CMonth=2  
  16. GROUP BY CUBE (TeamID,AreaID,CMonth)  
  17. ----ORDER  BY TeamID,AreaID,CMonth  


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

效果：

邀月工作室

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


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


邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助!   3w@live.cn

 

chelen_jak
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SqlServer2008实例10汇总数据
复行数十步
06-30 508
1.使用CUBE根据GROUP BY子句中的列来为结果集增加汇总值； CUBE为结果集增加行，根据GROUP BY子句巾的列来汇总总数值。下面示例演示了一个根据产品放置的货架束返回产品总数量的查询： SELECT i.Shelf,SUM(i.Quantity) Total FROM Production.ProductInventory i GROUP BY CUBE(i.Shelf) 在下一个查询中，我们将LocationID增加剑SELECT和GROUP BY子句中： USE Adve.
sql将日期区间拆分为多行
chinacmt的博客
04-25 86
日期拆分
sqlserver实现分类汇总
wangh92
08-01 1930
SELECT SUM ( CASE WHEN area_name IS NULL THEN 0 ELSE 1 END ) AS '有值', COUNT ( id ) AS "总数" FROM beike_property
SQL Server查询分类汇总
Backcanhave7
07-05 3668
构建测试数据： -- 判断是否已存在名为sales的表，若存在，则删除： if object_id('sales','U') is not null drop table sales -- 创建新的销售表sales: create table sales( year int, country varchar(20), product varchar(32), profit int )...
SQL Server 2008中的汇总计算技巧
lisky119的专栏
04-09 2011
SQL Server　2008中对汇总有明显的增强，有点像Oracle的语法了。请看下面五个例子： 假定场景如下：某几位员工在不同时间参加了不同的项目，获取了相应的收入，现在需要按各种分类进行统计。 基本表如下： [sql] view plaincopyprint? USE testDb2  GO    IF NOT OBJECT_ID('tb_Income')
客户回款数据库设计.doc
01-08
在这种情况下，企业为了避免由 于回款不力而陷入被动经营的怪圈，就必须加强回款工作的管理，提高回款工作的技巧 。本次设计针对这些复杂的关系和数据进行管理，绝对有利于厂商进行回款工作。有利 于客户和厂商的...
asp.net知识库
06-18
SQLSERVER高级注入技巧 利用反射实现ASP.NET控件和数据实体之间的双向绑定,并且在客户端自动验证输入的内容是否合法 asp.net报表解决方法 SQLDMO类的使用 SQL过程自动C#封装,支持从表到基本存储过程生成 使用SQLDMO...
Excel 2007数据透视表完全剖析 5/7
04-01
7.4.2　使用SQL Server数据构建数据透视表　170 7.5　下一步　174 第8章　与其他人共享数据透视表　175 8.1　与其他版本的Office共享数据透视表　175 8.1.1　Excel 2003数据透视表中不可用的功能　176 8.1.2　Excel...
Java开发实战1200例(第1卷).(清华出版.李钟尉.陈丹丹).part3
06-12
本书是第II卷，以开发人员在项目开发中经常遇到的问题和必须掌握的技术为中心，介绍了应用Java进行桌面程序开发各个方面的知识和技巧，主要包括Java语法与面向对象技术、Java高级应用、窗体与控件应用、文件操作...
sqlserver中的union,cube,rollup,cumpute运算符
一月三十一日
07-11 1232
UNION 运算符是将两个或更多查询的结果组合为单个结果集 该结果集包含联合查询中的所有查询的全部行。这与使用联接组合两个表中的列不同。   使用 UNION 组合查询的结果集有两个最基本的规则： 1。所有查询中的列数和列的顺序必须相同。 2。数据类型必须兼容 a.UNION的结果集列名与第一个select语句中的结果集中的列名相同，其他select语句的结果集列名被忽略 b.默认情况下，UNIO
SQL Server详细使用教程
qq_43341279的博客
04-20 735
本文详细介绍了SQL Server的使用教程，包括安装与配置、创建数据库和数据表、数据的增删改查等操作。同时，本文还介绍了数据库备份与还原以及数据库优化与维护等方面的知识，帮助用户提高数据库的稳定性和性能。在实际应用中，用户可以根据具体需求灵活运用SQL Server的各项功能，实现企业级数据管理的高效运作。它具备高性能、可扩展性、易用性等特点，是企业数据存储和处理的重要工具。本文将详细介绍SQL Server的使用教程，包括安装、配置、创建数据库、数据表、以及数据的增删改查等操作。九、数据库备份与还原。
oracle sql monitor简单使用说明
雅冰石的专栏
04-26 188
activity活动这里显示时间大多花在了哪里。
SQL 之 小技巧总结
最新发布
Orange_hhh的博客
04-26 150
eg：查看是否包含逗号（“,”）这里，% 是通配符，表示任意数量的任意字符。‘%,%’ 表示查询column_name中包含逗号（“,”）的任何值。
sqlserver 中分组汇总语句
09-07
在SQL Server中，分组汇总语句是一种用于根据特定字段对数据进行分组，并计算每个组内数据的汇总信息的查询语句。常用的分组汇总函数包括COUNT、SUM、AVG、MIN和MAX等。 例如，我们有一个名为"Orders"的表，包含...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值