SQL Server 2008中增强的汇总技巧

SQL Server 2008中SQL应用系列--目录索引

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
1    胡一刀    2011    2    5600.00    4
1    胡一刀    2011    1    5678.00    3
1    胡一刀    2011    3    6798.00    5
2    胡一刀    2011    4    7800.00    7
2    胡一刀    2011    5    8899.00    3
3    胡一刀    2012    8    8877.00    6
1    苗人凤    2011    1    3455.00    3
1    苗人凤    2011    2    4567.00    4
2    苗人凤    2011    3    5676.00    6
3    苗人凤    2011    4    5600.00    8
2    苗人凤    2011    5    6788.00    3
2    苗人凤    2012    6    5679.00    3
2    苗人凤    2012    7    6785.00    4
2    张无忌    2011    2    5600.00    5
2    张无忌    2011    3    2345.00    6
2    张无忌    2011    5    12000.00    3
3    张无忌    2011    4    23456.00    8
3    张无忌    2011    6    4567.00    5
1    张无忌    2012    7    6789.00    3
1    张无忌    2012    8    9998.00    4
3    赵半山    2011    7    6798.00    6
3    赵半山    2011    10    10000.00    4
3    赵半山    2011    9    12021.00    8
2    赵半山    2012    11    8799.00    3
1    赵半山    2012    12    10002.00    3
3    令狐冲    2011    8    7896.00    7
3    令狐冲    2011    9    7890.00    8
2    令狐冲    2011    10    7799.00    3
2    令狐冲    2011    11    9988.00    4
2    令狐冲    2012    9    34567.00    6
3    令狐冲    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负责将运算结果以整数形式返回。

效果:

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

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏沃趣科技

SQL优化案例-改变那些CBO无能为力的执行计划

用户写的sql,Oracle会进行等价改写,即使是RBO优化模式,Oracle也会给你做一些转换,这些转化都是基于一种固定的算法,oracle称这种转换是“启发...

47270
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(41)-组织架构

本节开始我们要实现工作流,此工作流可以和之前的所有章节脱离关系,也可以紧密合并。 我们当初设计的项目解决方案就是可伸缩可以拆离,可共享的项目解决方案。所以我们同...

22570
来自专栏杨建荣的学习笔记

关于索引扫描的极速调优实战(第二篇)(r3笔记第82天)

在上一篇http://blog.itpub.net/23718752/viewspace-1364914/ 中我们大体介绍了下问题的情况,已经初步根据awr能...

36570
来自专栏数据和云

元宵佳节:看Oracle技术粉们用SQL画团圆

话团圆,画团圆,元宵佳节倍思亲,可是大家知道吗,万能的SQL可以帮助大家绘制团圆。 在ITPUB论坛里,一群SQL爱好者们会用SQL来描摹一切可能。请看如下这段...

31350
来自专栏跟着阿笨一起玩NET

(1)显示每个类别最新更新的数据

在项目中经常遇到求每个类别最新显示的数据,比如显示某某某类别最新更新的5条数据。特写下这个sql记录于此:

9510
来自专栏landv

金蝶K/3 审批相关SQL语句

9010
来自专栏简书专栏

mysql实训

设有一个数据库,包括四个表:学生表(student)、课程表(course)、成绩表(score)以及教师信息表(teacher)。用SQL语句创建四个表并完成...

19310
来自专栏芋道源码1024

电商系统设计之订单

1. 前言2. 付款2.1 成功2.2 人祸2.4 天灾2.4 注释2.5 表结构2.5.1 交易表2.5.2 支付记录表2.5.3 订单表3. 运输4. 收货...

19130
来自专栏weixuqin 的专栏

SQL学习之查询

583100
来自专栏数据和云

案例分析:倾斜值传入导致 SQL 资源消耗升高

作者 | 邓秋爽:云和恩墨技术工程师,有超过七年超大型数据库专业服务经验,擅长 Oracle 数据库优化、SQL 优化和 Troubleshooting。

14540

扫码关注云+社区

领取腾讯云代金券