惯性聚合 高效追踪和阅读你感兴趣的博客、新闻、科技资讯
阅读原文 在惯性聚合中打开

推荐订阅源

Help Net Security
Help Net Security
G
Google Developers Blog
雷峰网
雷峰网
WordPress大学
WordPress大学
Threat Intelligence Blog | Flashpoint
Threat Intelligence Blog | Flashpoint
Engineering at Meta
Engineering at Meta
Security Latest
Security Latest
T
Threat Research - Cisco Blogs
AWS News Blog
AWS News Blog
F
Full Disclosure
C
Cybersecurity and Infrastructure Security Agency CISA
T
The Exploit Database - CXSecurity.com
J
Java Code Geeks
U
Unit 42
C
Cyber Attacks, Cyber Crime and Cyber Security
V
V2EX
C
Cisco Blogs
博
博客园 - 司徒正美
Project Zero
Project Zero
L
LINUX DO - 热门话题
阮一峰的网络日志
阮一峰的网络日志
Blog — PlanetScale
Blog — PlanetScale
Scott Helme
Scott Helme
A
About on SuperTechFans
Hugging Face - Blog
Hugging Face - Blog
S
Securelist
小众软件
小众软件
aimingoo的专栏
aimingoo的专栏
S
Schneier on Security
G
GRAHAM CLULEY
酷 壳 – CoolShell
酷 壳 – CoolShell
Cyberwarzone
Cyberwarzone
MongoDB | Blog
MongoDB | Blog
钛媒体：引领未来商业与生活新知
钛媒体：引领未来商业与生活新知
博
博客园 - 叶小钗
T
Threatpost
Recorded Future
Recorded Future
C
CXSECURITY Database RSS Feed - CXSecurity.com
宝玉的分享
宝玉的分享
N
News and Events Feed by Topic
人人都是产品经理
人人都是产品经理
The Register - Security
The Register - Security
S
Security Archives - TechRepublic
博
博客园 - Franky
N
News | PayPal Newsroom
Simon Willison's Weblog
Simon Willison's Weblog
S
SegmentFault 最新的问题
W
WeLiveSecurity
A
Arctic Wolf
B
Blog

博客园 - lsyyx

SQL Server 2008中SQL增强之三：Merge(在一条语句中使用Insert,Update,Delete) SQL Server 2008中SQL增强之二：Top新用途 SQL Server 2008中SQL增强之一：Values新用途 需要弥补的那部分SQL oracle递归查询\sqlserver递归 - lsyyx - 博客园 如何写SQL，计算上下记录同一字段相差值 SQL语句整理资料 Sqlserver Update 用法 可判断datatable的列是否有重复 Oracle：ODP.NET Managed 小试牛刀 (转) Asp.Net4.0/VS2010新变化(4):SEO的改进 Asp.Net4.0/VS2010新变化(2):网站自动预热 Asp.Net4.0/VS2010新变化(3):webform中也可以直接url路由 NET4.0中非常好用的一个东西Tuple 关于.net4.0中的Action委托 NET4.0多线程编程---Tasks .NET4.0多线程编程---Cooperative Cancellation net4.0新特性之线程同步 C#4.0语言新功能及应用 (1)
SQL Server 2008中增强的汇总技巧
lsyyx · 2015-01-13 · via 博客园 - lsyyx

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

posted on 2015-01-13 16:58  lsyyx  阅读(214)  评论()    收藏  举报

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。