SQL Server 2008中增强的汇总技巧

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

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

基本表如下：

USEtestDb2 GO IFNOTOBJECT_ID('tb_Income')ISNULLDROPTABLE[tb_Income] /******Object:Table[dbo].[tb_Income]ScriptDate:2012/4/58:19:21******/ CREATETABLE[dbo].[tb_Income]( [TeamID]intnotnull, [PName][Nvarchar](20)NOTNULL, [CYear]SmallintNOTNULL, [CMonth]TinyIntNOTNULL, [CMoney]Decimal(10,2)NotNull) GO INSERT[dbo].[tb_Income] SELECT1,'胡一刀',2011,2,5600 unionALLSELECT1,'胡一刀',2011,1,5678 unionALLSELECT1,'胡一刀',2011,3,6798 unionALLSELECT2,'胡一刀',2011,4,7800 unionALLSELECT2,'胡一刀',2011,5,8899 unionALLSELECT3,'胡一刀',2012,8,8877 unionALLSELECT1,'苗人凤',2011,1,3455 unionALLSELECT1,'苗人凤',2011,2,4567 unionALLSELECT2,'苗人凤',2011,3,5676 unionALLSELECT3,'苗人凤',2011,4,5600 unionALLSELECT2,'苗人凤',2011,5,6788 unionALLSELECT2,'苗人凤',2012,6,5679 unionALLSELECT2,'苗人凤',2012,7,6785 unionALLSELECT2,'张无忌',2011,2,5600 unionALLSELECT2,'张无忌',2011,3,2345 unionALLSELECT2,'张无忌',2011,5,12000 unionALLSELECT3,'张无忌',2011,4,23456 unionALLSELECT3,'张无忌',2011,6,4567 unionALLSELECT1,'张无忌',2012,7,6789 unionALLSELECT1,'张无忌',2012,8,9998 unionALLSELECT3,'赵半山',2011,7,6798 unionALLSELECT3,'赵半山',2011,10,10000 unionALLSELECT3,'赵半山',2011,9,12021 unionALLSELECT2,'赵半山',2012,11,8799 unionALLSELECT1,'赵半山',2012,12,10002 unionALLSELECT3,'令狐冲',2011,8,7896 unionALLSELECT3,'令狐冲',2011,9,7890 unionALLSELECT2,'令狐冲',2011,10,7799 unionALLSELECT2,'令狐冲',2011,11,9988 unionALLSELECT2,'令狐冲',2012,9,34567 unionALLSELECT3,'令狐冲',2012,12,5609 GO

数据如下：

SELECT*FROMtb_Income /* TeamIDPNameCYearCMonthCMoney 胡一刀201125600.00 胡一刀201115678.00 胡一刀201136798.00 胡一刀201147800.00 胡一刀201158899.00 胡一刀201288877.00 苗人凤201113455.00 苗人凤201124567.00 苗人凤201135676.00 苗人凤201145600.00 苗人凤201156788.00 苗人凤201265679.00 苗人凤201276785.00 张无忌201125600.00 张无忌201132345.00 张无忌2011512000.00 张无忌2011423456.00 张无忌201164567.00 张无忌201276789.00 张无忌201289998.00 赵半山201176798.00 赵半山20111010000.00 赵半山2011912021.00 赵半山2012118799.00 赵半山20121210002.00 令狐冲201187896.00 令狐冲201197890.00 令狐冲2011107799.00 令狐冲2011119988.00 令狐冲2012934567.00 令狐冲2012125609.00 */

一、使用CUBE汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

小试牛刀，

/*********使用CUBE汇总数据***************/ /*********[email protected]邀月***************/ SELECTTeamIDas小组ID, SUM(CMoney)总收入 FROMtb_Income GROUPBYCUBE(TeamID) ----ORDERBYTeamIDdesc

改进查询：

SELECTTeamIDas小组ID,PNameas姓名, SUM(CMoney)总收入 FROMtb_Income GROUPBYCUBE(TeamID,PName)

二、使用ROLLUP汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

/*********使用ROLLUP汇总数据***************/ /*********[email protected]邀月***************/ SELECTTeamIDas小组ID,PNameas姓名, SUM(CMoney)总收入 FROMtb_Income GROUPBYROLLUP(TeamID,PName)

注意：使用Rollup与指定的聚合列的顺序有关。

三、使用Grouping Sets创建自定义汇总数据（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）

除了Cube和Rollup，还有更加灵活强大的自定义集合汇总－－Grouping Sets

/*********使用GroupingSets创建自定义汇总数据***************/ /*********[email protected]邀月***************/ SELECTTeamIDas小组ID,PNameas姓名,CYearas年份,----min(CMonth)as月份, SUM(CMoney)总收入 FROMtb_Income WhereCMonth=2 GROUPBYgroupingSETS((TeamID),(TeamID,PName),(CYear,PName))

四、使用Grouping标识汇总行（http://technet.microsoft.com/zh-cn/library/ms178544.aspx）

细心的朋友可能会注意到，如果Cube后有两个以上的汇总列时，可能会有一些列是Null,那么这些Null值究竟本身就是Null,还是由于聚合产生的Null呢，此时，GroupingID函数大显身手的机会来了。

/*********使用Grouping标识汇总行***************/ /*********[email protected]邀月***************/ SELECTTeamIDas小组ID,CYearas年份, CASEWHENgrouping(TeamID)=0ANDgrouping(CYear)=1THEN'小组汇总'WHENgrouping(TeamID)=1ANDgrouping(CYear)=0THEN'年份汇总'WHENgrouping(TeamID)=1ANDgrouping(CYear)=1THEN'所有汇总'else'正常行'ENDas行类别, SUM(CMoney)总收入 FROMtb_Income GROUPBYCUBE(TeamID,CYear)

结果：

其实，还有更复杂的Grouping_ID，不过，一般情况下用不到，需要的同学，请看MSDN:

http://technet.microsoft.com/zh-cn/library/bb510624.aspx

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

原文链接：http://www.cnblogs.com/downmoon/archive/2012/04/06/2433988.html

【编辑推荐】

从SQL Server删除大数据说开去MySQL浅谈MyISAM存储引擎微软发布SQL Server 2012 RTM版本（附下载）MySQL服务端编码设置论NoSQL的数据模型