SQL Server 2008中SQL應(yīng)用系列--目錄索引SQL Server 2008中對匯總有明顯的增強有點像Oracle的語法了。請看下面五個例子假定場景如下某幾位員工在不同時間參加了不同的項目獲取了相應(yīng)的收入現(xiàn)在需要按各種分類進行統(tǒng)計?；颈砣缦耈SE 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數(shù)據(jù)如下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匯總數(shù)據(jù)http://msdn.microsoft.com/en-us/library/bb522495%28vsql.105%29.aspx小試牛刀/*********使用CUBE匯總數(shù)據(jù)***************/ /********* 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匯總數(shù)據(jù)Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS | Microsoft Learn/*********使用ROLLUP匯總數(shù)據(jù)***************/ /********* 3wlive.cn 邀月***************/ SELECT TeamID as 小組ID,PName as 姓名, SUM(CMoney) 總收入 FROM tb_Income GROUP BY ROLLUP (TeamID,PName)注意使用Rollup與指定的聚合列的順序有關(guān)。三、使用Grouping Sets創(chuàng)建自定義匯總數(shù)據(jù)Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS | Microsoft Learn除了Cube和Rollup還有更加靈活強大的自定義集合匯總Grouping Sets/*********使用Grouping Sets創(chuàng)建自定義匯總數(shù)據(jù)***************/ /********* 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,還是由于聚合產(chǎn)生的Null呢此時Grouping函數(shù)大顯身手的機會來了。/*********使用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類似但提供更為精細的顆粒度以確認分組級別當然使用也更為復(fù)雜請看下面的示例五、使用Grouping_ID標識分組級別http://technet.microsoft.com/zh-cn/library/bb510624.aspx為了更清楚地說明問題我們需要修改一下表結(jié)構(gòu)增加一個字段項目所在的地點AreaID,如下/*************修改表結(jié)構(gòu)***************************/ ALTER table tb_Income add AreaID int null GO update tb_Income SET AreaIDTeamIDCMonth%5CYear%2 GO此時數(shù)據(jù)變成這樣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 */我們需要統(tǒng)計小組、地區(qū)、月份三個維度的匯總數(shù)據(jù)。/*********使用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統(tǒng)計結(jié)果我們注意到由于維度從兩個變成三個此時數(shù)據(jù)比較凌亂即使排序也不能有效解決。幸好我們有Grouping_ID?？聪吕齋ELECT 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接受幾個輸入列返回二進制列列表計算的整數(shù)值你可以把這三個維度看作是0,1,1、0,1,0這樣類似的二進制而Grouping_ID負責(zé)將運算結(jié)果以整數(shù)形式返回。效果至此Group By的匯總暫時告一段落希望您不虛此行有所斬獲小結(jié)帶有Cube,Rollup,grouping Sets的Group By函數(shù)在統(tǒng)計與分析中有著廣泛的應(yīng)用相信它的高效簡捷在特定的場合會令人你愛不釋手邀月注本文版權(quán)由邀月和CSDN共同所有轉(zhuǎn)載請注明出處。助人等于自助! 3wlive.cn SQL Server 2008中增強的匯總技巧
SQL Server 2008中SQL應(yīng)用系列--目錄索引SQL Server 2008中對匯總有明顯的增強有點像Oracle的語法了。請看下面五個例子假定場景如下某幾位員工在不同時間參加了不同的項目獲取了相應(yīng)的收入現(xiàn)在需要按各種分類進行統(tǒng)計?；颈砣缦耈SE 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數(shù)據(jù)如下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匯總數(shù)據(jù)http://msdn.microsoft.com/en-us/library/bb522495%28vsql.105%29.aspx小試牛刀/*********使用CUBE匯總數(shù)據(jù)***************/ /********* 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匯總數(shù)據(jù)Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS | Microsoft Learn/*********使用ROLLUP匯總數(shù)據(jù)***************/ /********* 3wlive.cn 邀月***************/ SELECT TeamID as 小組ID,PName as 姓名, SUM(CMoney) 總收入 FROM tb_Income GROUP BY ROLLUP (TeamID,PName)注意使用Rollup與指定的聚合列的順序有關(guān)。三、使用Grouping Sets創(chuàng)建自定義匯總數(shù)據(jù)Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS | Microsoft Learn除了Cube和Rollup還有更加靈活強大的自定義集合匯總Grouping Sets/*********使用Grouping Sets創(chuàng)建自定義匯總數(shù)據(jù)***************/ /********* 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,還是由于聚合產(chǎn)生的Null呢此時Grouping函數(shù)大顯身手的機會來了。/*********使用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類似但提供更為精細的顆粒度以確認分組級別當然使用也更為復(fù)雜請看下面的示例五、使用Grouping_ID標識分組級別http://technet.microsoft.com/zh-cn/library/bb510624.aspx為了更清楚地說明問題我們需要修改一下表結(jié)構(gòu)增加一個字段項目所在的地點AreaID,如下/*************修改表結(jié)構(gòu)***************************/ ALTER table tb_Income add AreaID int null GO update tb_Income SET AreaIDTeamIDCMonth%5CYear%2 GO此時數(shù)據(jù)變成這樣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 */我們需要統(tǒng)計小組、地區(qū)、月份三個維度的匯總數(shù)據(jù)。/*********使用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統(tǒng)計結(jié)果我們注意到由于維度從兩個變成三個此時數(shù)據(jù)比較凌亂即使排序也不能有效解決。幸好我們有Grouping_ID?？聪吕齋ELECT 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接受幾個輸入列返回二進制列列表計算的整數(shù)值你可以把這三個維度看作是0,1,1、0,1,0這樣類似的二進制而Grouping_ID負責(zé)將運算結(jié)果以整數(shù)形式返回。效果至此Group By的匯總暫時告一段落希望您不虛此行有所斬獲小結(jié)帶有Cube,Rollup,grouping Sets的Group By函數(shù)在統(tǒng)計與分析中有著廣泛的應(yīng)用相信它的高效簡捷在特定的場合會令人你愛不釋手邀月注本文版權(quán)由邀月和CSDN共同所有轉(zhuǎn)載請注明出處。助人等于自助! 3wlive.cn