开发者社区> 缤纷世界wb> 正文

SQL Server 2008的汇总技巧

简介: SQL Server 2008中对汇总有明显的增强,有点像Oracle的语法了。请看下面五个例子: 假定场景如下:某几位员工在不同时间参加了不同的项目,获取了相应的收入,现在需要按各种分类进行统计。 基本表如下: View Code USE testDb2GOIF NOT OBJE...
+关注继续查看

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

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

基本表如下:

View Code
复制代码
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函数在统计与分析中有着广泛的应用,相信它的高效简捷,在特定的场合会令你爱不释手!

 

【转】邀月工作室

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Mysql实战常见问题汇总
引言 项目中使用Mysql经常会遇到修改编码格式、导入sql、导出sql、windows授权、查看删除死锁进程等操作。 特总结如下:
17 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
17900 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
19591 0
MS SQL查询库、表、列数据结构信息汇总
前言 一般情况我们下,我们是知道数据库的表、列信息的(因为数据库是我们手动设计),但特殊情况下,如果你只能拿到数据库连接信息,也就是知道的一个数据库名的情况下,你要怎么得到它下面的所有表名,所有列表,以及主键,是否自增?等这些信息,那么本文将给您揭晓这个答案。
641 0
MS-SQL数据库开发常用汇总 (收藏)
 MS-SQL数据库开发常用汇总 1.按姓氏笔画排序: Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 2.
916 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
8949 0
MySQL 数据库备份种类以及常用备份工具汇总
  1,数据库备份种类 按照数据库大小备份,有四种类型,分别应用于不同场合,下面简要介绍一下: 1.1完全备份 这是大多数人常用的方式,它可以备份整个数据库,包含用户表、系统表、索引、视图和存储过程等所有数据库对象。
1222 0
+关注
缤纷世界wb
什么都会一点儿,但只是会一点儿
214
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载