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
胡一刀 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负责将运算结果以整数形式返回。
效果：
至此，Group By的汇总暂时告一段落，希望您不虚此行，有所斩获！
小结：带有Cube,Rollup,grouping Sets的Group By函数在统计与分析中有着广泛的应用，相信它的高效简捷，在特定的场合会令人你爱不释手！
邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助! 3w@live.cn
分享到：
相关推荐
在SQL Server 2008中，T-SQL得到了进一步的增强和优化，提供了更多的功能和性能提升。以下是一些关键的知识点： 1. **基础查询语法**：包括SELECT语句的基本结构，用于从一个或多个表中检索数据。学习如何使用WHERE...
除此之外，手册还提及了SQL Server 2008 R2中一些不为人知的功能和细节，比如如何在运行中合理分配CPU和内存资源，以及一些常见问题的汇总。这些内容对于数据库管理员来说非常有帮助，尤其是对于优化和故障排除方面...
本书深入讲解了T-SQL的基础知识及其在SQL Server 2008中的应用。 #### 三、T-SQL查询技巧 - **基本查询**：本书首先介绍了如何使用SELECT语句进行基本的数据检索操作，包括简单的数据选择、过滤和排序。 - **复杂...
在SQL Server 2008中，T-SQL得到了进一步的增强和优化，以提升性能和功能。 本书可能包含以下核心知识点： 1. **T-SQL基础**：包括SELECT语句的使用，用于从数据库中检索数据；INSERT、UPDATE和DELETE语句，用于...
本课程旨在为学生提供使用Microsoft SQL Server 2008中的Transact-SQL语言编写基本查询所需的技能与知识。课程结束后，学员将能够熟练地使用查询工具，并通过SELECT语句来检索和修改数据。此外，他们还将学会如何...
以上只是SQL Server开发中的一部分知识点，实际应用中还有很多其他高级特性和实践技巧值得深入学习。对于初学者来说，建议从基础语法入手，逐步掌握SQL Server的核心功能，并结合具体项目进行实战练习，这样才能更好...
- **最佳实践：** 汇总了一系列经过验证的最佳实践，帮助用户实现更加高效稳定的SQL Server环境。 **2.9 在虚拟环境中运行SQL Server** - **虚拟化技术：** 第十七章讨论了如何在虚拟化环境中部署和运行SQL Server...
《Microsoft SQL Server 2008 MDX Step by Step》这本书全面而深入地介绍了 MDX 语言的核心概念和实践技巧。从基础的元组和集合到复杂的聚合函数和层次结构导航，读者可以逐步学习并掌握这些技能。此外，本书还提供...
《SQL Server T-SQL Recipes, 4th Edition》不仅是一本技术手册，更是一本全面覆盖SQL Server各种操作和优化技巧的实用指南。无论是初学者还是经验丰富的开发者，都能从中找到有价值的参考信息。通过学习本书，读者...
以上技巧涵盖了SQL Server中的基础操作、数据管理、安全性控制以及性能优化等多个方面，对于日常开发与数据库维护工作非常实用。掌握这些技巧，可以显著提升SQL查询的效率与准确性，同时增强数据库的安全性和稳定性...
SQL Server 存储过程是数据库管理中的一种核心功能，它是一组预先编译的SQL语句集合，能够执行复杂的数据库操作。存储过程不仅提高了程序的运行效率，还能增强数据库的安全性，因为它允许限制对特定数据的访问。在...
综上所述，《Professional SQL Server 2005 CLR Programming》是一本详尽介绍如何在SQL Server 2005中使用CLR技术编写高效、安全的应用程序的书籍。通过深入理解这些核心概念和技术细节，开发者能够更好地利用SQL ...
- **报告服务简介**：本章节详细介绍了Microsoft SQL Server 2012 Reporting Services（以下简称“RS”）的基础概念，包括其在商业智能（BI）中的作用、特点以及与其他BI组件的关系等。对于初学者来说，这部分内容是...
Voytek II合著，出版于2006年，旨在深入解析Microsoft SQL Server 2005 Reporting Services的各项功能与应用技巧。此书是专业级别的指南，专为希望掌握SQL Server 2005 Reporting Services的高级特性和最佳实践的...
根据给定的文件标题、描述、标签以及部分内容来看，虽然部分内容并未给出具体的知识点，但从标题“SQLServer数据库设计和高级查询13”可以推测出本篇内容将围绕SQL Server数据库的设计原则与高级查询技巧展开。...
描述中提到“非常实用的Sql语句，适用于SqlServer”，这暗示了这些SQL语句可能专注于在微软的SQL Server数据库管理系统上进行操作。 标签“delphi___sql”进一步确认了这是关于在Delphi编程环境中使用SQL的教程或...