SQL Server 2008中对汇总有明显的增强,有点像Oracle的语法了。请看下面五个例子:
假定场景如下:某几位员工在不同时间参加了不同的项目,获取了相应的收入,现在需要按各种分类进行统计。
基本表如下:
- 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
- 1 胡一刀 2011 2 5600.00
- 1 胡一刀 2011 1 5678.00
- 1 胡一刀 2011 3 6798.00
- 2 胡一刀 2011 4 7800.00
- 2 胡一刀 2011 5 8899.00
- 3 胡一刀 2012 8 8877.00
- 1 苗人凤 2011 1 3455.00
- 1 苗人凤 2011 2 4567.00
- 2 苗人凤 2011 3 5676.00
- 3 苗人凤 2011 4 5600.00
- 2 苗人凤 2011 5 6788.00
- 2 苗人凤 2012 6 5679.00
- 2 苗人凤 2012 7 6785.00
- 2 张无忌 2011 2 5600.00
- 2 张无忌 2011 3 2345.00
- 2 张无忌 2011 5 12000.00
- 3 张无忌 2011 4 23456.00
- 3 张无忌 2011 6 4567.00
- 1 张无忌 2012 7 6789.00
- 1 张无忌 2012 8 9998.00
- 3 赵半山 2011 7 6798.00
- 3 赵半山 2011 10 10000.00
- 3 赵半山 2011 9 12021.00
- 2 赵半山 2012 11 8799.00
- 1 赵半山 2012 12 10002.00
- 3 令狐冲 2011 8 7896.00
- 3 令狐冲 2011 9 7890.00
- 2 令狐冲 2011 10 7799.00
- 2 令狐冲 2011 11 9988.00
- 2 令狐冲 2012 9 34567.00
- 3 令狐冲 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呢,此时,Grouping函数大显身手的机会来了。
- /*********使用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,它与Grouping类似,但提供更为精细的颗粒度,以确认分组级别,当然使用也更为复杂,请看下面的示例:
五、使用Grouping_ID标识分组级别(http://technet.microsoft.com/zh-cn/library/bb510624.aspx)
为了更清楚地说明问题,我们需要修改一下表结构,增加一个字段--项目所在的地点(AreaID),如下:
- /*************修改表结构***************************/
- ALTER table tb_Income
- add AreaID int null
- GO
- update tb_Income SET AreaID=TeamID+CMonth%5+CYear%2
- GO
此时数据变成这样:
- 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
- */
我们需要统计小组、地区、月份三个维度的汇总数据。
- /*********使用Grouping_ID标识分组级别***************/
- /********* 3w@live.cn 邀月***************/
- 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=2
- GROUP BY CUBE (TeamID,AreaID,CMonth)
- ----ORDER BY TeamID,AreaID,CMonth
统计结果:
我们注意到,由于维度从两个变成三个,此时数据比较凌乱,即使排序也不能有效解决。幸好,我们有Grouping_ID。看下例:
- 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=2
- GROUP BY CUBE (TeamID,AreaID,CMonth)
- ----ORDER BY TeamID,AreaID,CMonth
注意:代码中新增的部分,这里需要稍微解释一下,Grouping_ID接受几个输入列,返回二进制列列表计算的整数值,你可以把这三个维度,看作是(0,1,1)、(0,1,0)这样类似的二进制,而Grouping_ID负责将运算结果以整数形式返回。
效果:
![邀月工作室](https://i-blog.csdnimg.cn/blog_migrate/adfb62dd541fb193f63e82e94a2b0cea.png)
至此,Group By的汇总暂时告一段落,希望您不虚此行,有所斩获!