SQL Server 2008中的汇总计算技巧

最新推荐文章于 2023-08-31 21:34:40 发布
最新推荐文章于 2023-08-31 21:34:40 发布
阅读量2k 收藏
点赞数
分类专栏： DB数据库 文章标签： sql server null sql object table go

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. ) 
  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 
  57.  
  58.   


数据如下：

[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.  
  6. GO 
  7.  
  8. update tb_Income SET AreaID=TeamID+CMonth%5+CYear%2 
  9. 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
lisky119
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL查询技巧（范例宝典）
06-04
　实例362　静态交叉表（SQLServer 2000）　531 　实例363　动态交叉表（SQLServer 2000）　533 　10.19　函数查询　535 　实例364　在查询语句中使用格式化函数　536 　实例365　在查询语句中使用字符串函数　...
sql server 分组后实现多列合并
NC_star的博客
08-28 5019
在sql语句查询中有这样一种情况，将分组后的数据中的某一列的值进行合并，然后显示出来。大概场景如下： 有一张用户表： 根据这张用户表，按照年龄分组，然后把年龄为30岁的人名字显示在一行中，并用逗号隔开。如果是oracle数据，这个需求很容易实现，可以通过oracle的wm_concat来实现。但是在sql server中不支持这个函数，那么可以用如下语句实现： 查询结果如下： ...
SqlServer2008实例10汇总数据
复行数十步
06-30 506
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中的分组汇总的三个方式
清雅小屋
12-27 3981
Sql
SQL 如何合并两列数据为一列数据
LP'S Blog
08-31 9535
在本文中，我们将介绍如何在SQL中将两列数据合并为一列数据。在很多情况下，我们需要将两个列中的数据合并为一个列，以便更好地分析和处理数据。在本文中，我们介绍了五种将两列数据合并为一列数据的方法：使用CONCAT函数、使用加法操作符、使用CASE语句、使用UNION操作符。根据实际需求，选择合适的方法可以更好地处理和分析数据。在使用这些方法时，需要注意列的数据类型和兼容性，以确保合并操作的准确性和可靠性。
【分组后的统计及求和的SQL】
chenghonglei123的博客
04-15 1万+
1.1分组后的合计SQL语句1： SELECT COUNT(*) FROM （SELECT 分组字段 FROM 表 GROUP BY 分组字段 ）别名 1.2分组后的合计SQL语句2： SELECT COUNT(*) FROM （SELECT distinct 分组字段 FROM 表）别名 2.1分组后的求和SQL语句： SELECT SUM(*) FROM （SELECT 分组字段 FROM 表 GROUP BY 分组字段 ）别名 ...
程序员的SQL金典.rar
03-29
　本书特色：主要介绍SQL的语法规则及在实际开发中的应用，并且对SQL在MySQL、MS SQL Server、Oracle和DB2中的差异进行了分析；详细讲解数据库对增、删、改、查等SQL的支持并给出了相应的SQL应用案例；透彻分析函数...
21天学习SQL V1.0
11-09
21天学习SQL V1.0.pdf 66 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL [email protected] 2 日期/时间函数.........................................................................................................
SQL21日自学通
12-19
计算所有的表中的行数354 为多个用户赋予系统权限359 将你的表的权限赋予其它的用户361 在载入数据时解除对数的约束363 一次创建多个同义字364 为你的表创建视图368 在一个计划中清除其所有的表的内容369 使用SQL 来...
SQL必知必会(第3版)--详细书签版
08-21
读者将从本书中循序渐进、系统、直接地学到SQL的知识和技巧。 　这是本书的第3版，它已经教会了成千上万的读者使用SQL。现在轮到你了，让我们翻到第1章，开始学习吧。你将很快编写出世界级的SQL。 　读者对象 　本书...
单条SQL语句实现复杂逻辑几例
10-15
单条SQL语句实现复杂逻辑几例。本文简单明了，介绍了用单条SQL语句，就能实现复杂逻辑的例子。比较有用。
sql server中分组合并列值
minixu的博客
10-11 1万+
表a 我们想让班级相同的合并成一列查出Name SELECT Class,STUFF( ( SELECT ','+ Name FROM a b WHERE b.Class = a.Class FOR XML PATH('')),1 ,1, '') Name FROM a a GROUP BY Class 我们如果想查出Age,因为age是int类型的 我们需
SQL Server汇总数据之聚合函数与分组
焚心似火
12-25 6428
主要用于对数据集的的数据进行汇总统计等操作，基本是聚合函数。 聚合的基本理念：不是返回所有指定的行，而是返回一行计算得到的值（前面指定的行 的某些数据的汇总）。它们汇总了原始数据集。 1、计算总数、平均值 2、统计分析 3、解决聚合问题 4、创建动态的交叉查询 一、简单聚合 在SQL查询的逻辑流程中，聚合函数是在From子句和Where子句之后执
sql //通过分组把组中某一字段拼接 // 拼接 行列转换 //分组统计
weixin_44178762的博客
09-02 838
SELECT * from ( select equipment_code,data_time ,site_name, SUM...
sql server2008根据分组，求每组总个数
qq_33172029的博客
01-02 811
数据库：sql server2008 代码： select mytype,COUNT(*) as thecount from ( select (case when isNull((AllPay+performancePay ),0) >= 0 and isNull((AllPay+performancePay ),0) < 5000 then 'A' when isNull...
SQL语句（包含:求统计值、合格率、合格次数、保留两位小数等）
殇莫忆的博客
08-08 1万+
表的结构 【这张表---- low 是上限值  ； high 是下限值 ； __id  是第二张表的a  互相关联的表】 【第二张表】 求多个?的值 ①、函数min、max、avg、count 等  【a , b ， tag, low, high 分别是两张表的字段】 select 字段 ,count(字段) as count_a , min(字段) as min_b , ...
sql题目，统计每个班级考试成绩的合格率，优秀率
热门推荐
Draco
07-22 2万+
一张表存储了学生的成绩，班级号等信息，统计各个班的合格率（>=60)、优秀率(>=80)。要考虑到有的班级没有人通过或者合格的情况。 建表： create table score(s_id number(10),num number(3),class_id number(10)); 插入测试数据： insert into score values(1,90,1001); insert int
GBase 8a SQL学习：统计各系、班级学生各科成绩不合格率，合格率排行的方法
jingjing1068的博客
01-19 1751
合格的判定 每个科目设定一个合格的最低标准，低于标准则为不合格。 学生(S) 8个学生，平均分散在2个系，4个班级里面。 create table s(Sno int ,Sname varchar(100),Ssex int,Sage int,Sdept varchar(100),Sclass varchar(100)); -- 表中属性分别为：学号、姓名、性别、年龄和系名。 insert into s values(1,'张1',1,18,'计算机系','计算机系1班'); insert into s v
SQL sever 查询及格率
capsule的博客
03-14 1万+
首先需要用到cast函数或者round函数 CAST (expression AS data_type) 参数说明： expression：任何有效的SQServer表达式。 AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。 data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。 使用CAST函数进行数...
sqlserver 中分组汇总语句
最新发布
09-07
在SQL Server中，分组汇总语句是一种用于根据特定字段对数据进行分组，并计算每个组内数据的汇总信息的查询语句。常用的分组汇总函数包括COUNT、SUM、AVG、MIN和MAX等。 例如，我们有一个名为"Orders"的表，包含订单号、客户编号和订单金额等字段。我们可以使用分组汇总语句来计算每个客户的订单总金额。 ```sql SELECT CustomerID, SUM(OrderAmount) AS TotalAmount FROM Orders GROUP BY CustomerID ``` 以上语句中，使用了SUM函数来计算每个客户的订单总金额，并使用GROUP BY子句将结果按照客户编号分组。最后，通过SELECT语句选择显示客户编号和总金额。 除了SUM函数，我们还可以使用其他分组汇总函数。例如，如果我们想知道每个客户的订单数量，可以使用COUNT函数。 ```sql SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders GROUP BY CustomerID ``` 以上语句中，使用了COUNT函数来计算每个客户的订单数量，并通过GROUP BY子句按照客户编号进行分组。 分组汇总语句还可以使用HAVING子句来过滤汇总结果。例如，如果我们只想查找订单总金额大于1000的客户，可以使用HAVING子句来实现。 ```sql SELECT CustomerID, SUM(OrderAmount) AS TotalAmount FROM Orders GROUP BY CustomerID HAVING SUM(OrderAmount) > 1000 ``` 以上语句中，使用HAVING子句来过滤总金额大于1000的客户。 总而言之，SQL Server中的分组汇总语句是一种强大的工具，可以根据特定字段对数据进行分组，并计算每个组的汇总信息。通过使用合适的分组汇总函数和HAVING子句，我们可以根据需求来获取到所需的汇总结果。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值