程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server 2008中增強的匯總技巧

SQL Server 2008中增強的匯總技巧

編輯:關於SqlServer

SQL Server 2008中對匯總有明顯的增強,有點像Oracle的語法了。請看下面四個例子:

假定場景如下:某幾位員工在不同時間參加了不同的項目,獲取了相應的收入,現在需要按各種分類進行統計。

基本表如下:

  1. USE testDb2
  2. GO
  3. IF NOT OBJECT_ID('tb_Income') IS NULL
  4. DROP TABLE [tb_Income]
  5. /****** Object: Table [dbo].[tb_Income] Script Date: 2012/4/5 8:19:21 ******/
  6. CREATE TABLE [dbo].[tb_Income](
  7. [TeamID] int not null,
  8. [PName] [Nvarchar](20) NOT NULL,
  9. [CYear] Smallint NOT NULL,
  10. [CMonth] TinyInt NOT NULL,
  11. [CMoney] Decimal (10,2) Not Null
  12. )
  13. GO
  14. INSERT [dbo].[tb_Income]
  15. SELECT 1,'胡一刀',2011,2,5600
  16. union ALL SELECT 1,'胡一刀',2011,1,5678
  17. union ALL SELECT 1,'胡一刀',2011,3,6798
  18. union ALL SELECT 2,'胡一刀',2011,4,7800
  19. union ALL SELECT 2,'胡一刀',2011,5,8899
  20. union ALL SELECT 3,'胡一刀',2012,8,8877
  21. union ALL SELECT 1,'苗人鳳',2011,1,3455
  22. union ALL SELECT 1,'苗人鳳',2011,2,4567
  23. union ALL SELECT 2,'苗人鳳',2011,3,5676
  24. union ALL SELECT 3,'苗人鳳',2011,4,5600
  25. union ALL SELECT 2,'苗人鳳',2011,5,6788
  26. union ALL SELECT 2,'苗人鳳',2012,6,5679
  27. union ALL SELECT 2,'苗人鳳',2012,7,6785
  28. union ALL SELECT 2,'張無忌',2011,2,5600
  29. union ALL SELECT 2,'張無忌',2011,3,2345
  30. union ALL SELECT 2,'張無忌',2011,5,12000
  31. union ALL SELECT 3,'張無忌',2011,4,23456
  32. union ALL SELECT 3,'張無忌',2011,6,4567
  33. union ALL SELECT 1,'張無忌',2012,7,6789
  34. union ALL SELECT 1,'張無忌',2012,8,9998
  35. union ALL SELECT 3,'趙半山',2011,7,6798
  36. union ALL SELECT 3,'趙半山',2011,10,10000
  37. union ALL SELECT 3,'趙半山',2011,9,12021
  38. union ALL SELECT 2,'趙半山',2012,11,8799
  39. union ALL SELECT 1,'趙半山',2012,12,10002
  40. union ALL SELECT 3,'令狐沖',2011,8,7896
  41. union ALL SELECT 3,'令狐沖',2011,9,7890
  42. union ALL SELECT 2,'令狐沖',2011,10,7799
  43. union ALL SELECT 2,'令狐沖',2011,11,9988
  44. union ALL SELECT 2,'令狐沖',2012,9,34567
  45. union ALL SELECT 3,'令狐沖',2012,12,5609
  46. GO

數據如下:

  1. SELECT * FROM tb_Income
  2. /*
  3. TeamID PName CYear CMonth CMoney
  4. 胡一刀 2011 2 5600.00
  5. 胡一刀 2011 1 5678.00
  6. 胡一刀 2011 3 6798.00
  7. 胡一刀 2011 4 7800.00
  8. 胡一刀 2011 5 8899.00
  9. 胡一刀 2012 8 8877.00
  10. 苗人鳳 2011 1 3455.00
  11. 苗人鳳 2011 2 4567.00
  12. 苗人鳳 2011 3 5676.00
  13. 苗人鳳 2011 4 5600.00
  14. 苗人鳳 2011 5 6788.00
  15. 苗人鳳 2012 6 5679.00
  16. 苗人鳳 2012 7 6785.00
  17. 張無忌 2011 2 5600.00
  18. 張無忌 2011 3 2345.00
  19. 張無忌 2011 5 12000.00
  20. 張無忌 2011 4 23456.00
  21. 張無忌 2011 6 4567.00
  22. 張無忌 2012 7 6789.00
  23. 張無忌 2012 8 9998.00
  24. 趙半山 2011 7 6798.00
  25. 趙半山 2011 10 10000.00
  26. 趙半山 2011 9 12021.00
  27. 趙半山 2012 11 8799.00
  28. 趙半山 2012 12 10002.00
  29. 令狐沖 2011 8 7896.00
  30. 令狐沖 2011 9 7890.00
  31. 令狐沖 2011 10 7799.00
  32. 令狐沖 2011 11 9988.00
  33. 令狐沖 2012 9 34567.00
  34. 令狐沖 2012 12 5609.00
  35. */

一、使用CUBE匯總數據(http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.ASPx)

小試牛刀,

  1. /*********使用CUBE匯總數據***************/
  2. /********* [email protected] 邀月***************/
  3. SELECT TeamID as 小組ID,
  4. SUM(CMoney) 總收入
  5. FROM tb_Income
  6. GROUP BY CUBE (TeamID)
  7. ----ORDER BY TeamID desc


邀月工作室

改進查詢:

  1. SELECT TeamID as 小組ID,PName as 姓名,
  2. SUM(CMoney) 總收入
  3. FROM tb_Income
  4. GROUP BY CUBE (TeamID,PName)

邀月工作室

二、使用ROLLUP匯總數據(http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.ASPx)

  1. /*********使用ROLLUP匯總數據***************/
  2. /********* [email protected] 邀月***************/
  3. SELECT TeamID as 小組ID,PName as 姓名,
  4. SUM(CMoney) 總收入
  5. FROM tb_Income
  6. 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

  1. /*********使用Grouping Sets創建自定義匯總數據***************/
  2. /********* [email protected] 邀月***************/
  3. SELECT TeamID as 小組ID,PName as 姓名,CYear as 年份,----min(CMonth) as 月份,
  4. SUM(CMoney) 總收入
  5. FROM tb_Income
  6. Where CMonth=2
  7. GROUP BY grouping SETS ((TeamID),(TeamID,PName),(CYear,PName))

邀月工作室

四、使用Grouping標識匯總行(http://technet.microsoft.com/zh-cn/library/ms178544.ASPx)

細心的朋友可能會注意到,如果Cube後有兩個以上的匯總列時,可能會有一些列是Null,那麼這些Null值究竟本身就是Null,還是由於聚合產生的Null呢,此時,GroupingID函數大顯身手的機會來了。

  1. /*********使用Grouping標識匯總行***************/
  2. /********* [email protected] 邀月***************/
  3. SELECT TeamID as 小組ID,CYear as 年份,
  4. CASE WHEN grouping(TeamID)=0 AND grouping(CYear)=1 THEN '小組匯總'
  5. WHEN grouping(TeamID)=1 AND grouping(CYear)=0 THEN '年份匯總'
  6. WHEN grouping(TeamID)=1 AND grouping(CYear)=1 THEN '所有匯總'
  7. else '正常行' END as 行類別,
  8. SUM(CMoney) 總收入
  9. FROM tb_Income
  10. GROUP BY CUBE (TeamID,CYear)

結果:

邀月工作室

其實,還有更復雜的Grouping_ID,不過,一般情況下用不到,需要的同學,請看MSDN:

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

小結:帶有Cube,Rollup,grouping Sets的Group By函數在統計與分析中有著廣泛的應用,相信它的高效簡捷,在特定的場合會令人你愛不釋手!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved