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%28vsql.105%29.aspx小试牛刀/*********使用CUBE汇总数据***************/ /********* 3wlive.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汇总数据Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS | Microsoft Learn/*********使用ROLLUP汇总数据***************/ /********* 3wlive.cn 邀月***************/ SELECT TeamID as 小组ID,PName as 姓名, SUM(CMoney) 总收入 FROM tb_Income GROUP BY ROLLUP (TeamID,PName)注意使用Rollup与指定的聚合列的顺序有关。三、使用Grouping Sets创建自定义汇总数据Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS | Microsoft Learn除了Cube和Rollup还有更加灵活强大的自定义集合汇总Grouping Sets/*********使用Grouping Sets创建自定义汇总数据***************/ /********* 3wlive.cn 邀月***************/ SELECT TeamID as 小组ID,PName as 姓名,CYear as 年份,----min(CMonth) as 月份, SUM(CMoney) 总收入 FROM tb_Income Where CMonth2 GROUP BY grouping SETS ((TeamID),(TeamID,PName),(CYear,PName))四、使用Grouping标识汇总行分组Transact-SQL - SQL Server | Microsoft Learn细心的朋友可能会注意到如果Cube后有两个以上的汇总列时可能会有一些列是Null,那么这些Null值究竟本身就是Null,还是由于聚合产生的Null呢此时Grouping函数大显身手的机会来了。/*********使用Grouping标识汇总行***************/ /********* 3wlive.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 AreaIDTeamIDCMonth%5CYear%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标识分组级别***************/ /********* 3wlive.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 CMonth2 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 CMonth2 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共同所有转载请注明出处。助人等于自助! 3wlive.cn 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%28vsql.105%29.aspx小试牛刀/*********使用CUBE汇总数据***************/ /********* 3wlive.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汇总数据Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS | Microsoft Learn/*********使用ROLLUP汇总数据***************/ /********* 3wlive.cn 邀月***************/ SELECT TeamID as 小组ID,PName as 姓名, SUM(CMoney) 总收入 FROM tb_Income GROUP BY ROLLUP (TeamID,PName)注意使用Rollup与指定的聚合列的顺序有关。三、使用Grouping Sets创建自定义汇总数据Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS | Microsoft Learn除了Cube和Rollup还有更加灵活强大的自定义集合汇总Grouping Sets/*********使用Grouping Sets创建自定义汇总数据***************/ /********* 3wlive.cn 邀月***************/ SELECT TeamID as 小组ID,PName as 姓名,CYear as 年份,----min(CMonth) as 月份, SUM(CMoney) 总收入 FROM tb_Income Where CMonth2 GROUP BY grouping SETS ((TeamID),(TeamID,PName),(CYear,PName))四、使用Grouping标识汇总行分组Transact-SQL - SQL Server | Microsoft Learn细心的朋友可能会注意到如果Cube后有两个以上的汇总列时可能会有一些列是Null,那么这些Null值究竟本身就是Null,还是由于聚合产生的Null呢此时Grouping函数大显身手的机会来了。/*********使用Grouping标识汇总行***************/ /********* 3wlive.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 AreaIDTeamIDCMonth%5CYear%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标识分组级别***************/ /********* 3wlive.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 CMonth2 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 CMonth2 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共同所有转载请注明出处。助人等于自助! 3wlive.cn