SQL Server 2008中增強(qiáng)的匯總技巧
SQL Server 2008中對匯總有明顯的增強(qiáng)，有點(diǎn)像Oracle的語法了。請看下面四個例子：
假定場景如下：某幾位員工在不同時間參加了不同的項(xiàng)目，獲取了相應(yīng)的收入，現(xiàn)在需要按各種分類進(jìn)行統(tǒng)計(jì)。
基本表如下：
- 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
數(shù)據(jù)如下：
- SELECT * FROM tb_Income
- /*
- TeamID PName CYear CMonth CMoney
- 胡一刀 2011 2 5600.00
- 胡一刀 2011 1 5678.00
- 胡一刀 2011 3 6798.00
- 胡一刀 2011 4 7800.00
- 胡一刀 2011 5 8899.00
- 胡一刀 2012 8 8877.00
- 苗人鳳 2011 1 3455.00
- 苗人鳳 2011 2 4567.00
- 苗人鳳 2011 3 5676.00
- 苗人鳳 2011 4 5600.00
- 苗人鳳 2011 5 6788.00
- 苗人鳳 2012 6 5679.00
- 苗人鳳 2012 7 6785.00
- 張無忌 2011 2 5600.00
- 張無忌 2011 3 2345.00
- 張無忌 2011 5 12000.00
- 張無忌 2011 4 23456.00
- 張無忌 2011 6 4567.00
- 張無忌 2012 7 6789.00
- 張無忌 2012 8 9998.00
- 趙半山 2011 7 6798.00
- 趙半山 2011 10 10000.00
- 趙半山 2011 9 12021.00
- 趙半山 2012 11 8799.00
- 趙半山 2012 12 10002.00
- 令狐沖 2011 8 7896.00
- 令狐沖 2011 9 7890.00
- 令狐沖 2011 10 7799.00
- 令狐沖 2011 11 9988.00
- 令狐沖 2012 9 34567.00
- 令狐沖 2012 12 5609.00
- */
一、使用CUBE匯總數(shù)據(jù)（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）
小試牛刀，
- /*********使用CUBE匯總數(shù)據(jù)***************/
- /********* 3w@live.cn 邀月***************/
- SELECT TeamID as 小組ID,
- SUM(CMoney) 總收入
- FROM tb_Income
- GROUP BY CUBE (TeamID)
- ----ORDER BY TeamID desc
改進(jìn)查詢：
- SELECT TeamID as 小組ID,PName as 姓名,
- SUM(CMoney) 總收入
- FROM tb_Income
- GROUP BY CUBE (TeamID,PName)
二、使用ROLLUP匯總數(shù)據(jù)（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）
- /*********使用ROLLUP匯總數(shù)據(jù)***************/
- /********* 3w@live.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ù)（http://msdn.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx）
除了Cube和Rollup，還有更加靈活強(qiáng)大的自定義集合匯總－－Grouping Sets
- /*********使用Grouping Sets創(chuàng)建自定義匯總數(shù)據(jù)***************/
- /********* 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標(biāo)識匯總行（http://technet.microsoft.com/zh-cn/library/ms178544.aspx）
細(xì)心的朋友可能會注意到，如果Cube后有兩個以上的匯總列時，可能會有一些列是Null,那么這些Null值究竟本身就是Null,還是由于聚合產(chǎn)生的Null呢，此時，GroupingID函數(shù)大顯身手的機(jī)會來了。
- /*********使用Grouping標(biāo)識匯總行***************/
- /********* 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)
結(jié)果：
其實(shí)，還有更復(fù)雜的Grouping_ID，不過，一般情況下用不到，需要的同學(xué)，請看MSDN:
http://technet.microsoft.com/zh-cn/library/bb510624.aspx
小結(jié)：帶有Cube,Rollup,grouping Sets的Group By函數(shù)在統(tǒng)計(jì)與分析中有著廣泛的應(yīng)用，相信它的高效簡捷，在特定的場合會令人你愛不釋手！
原文鏈接：http://www.cnblogs.com/downmoon/archive/2012/04/06/2433988.html
【編輯推薦】
