SQL Server關(guān)于WITH CUBE、WITH ROLLUP和GROUPING使用
通過查看sql 2005的幫助文檔找到了CUBE 和 ROLLUP 之間的具體區(qū)別：
CUBE 生成的結(jié)果集顯示了所選列中值的所有組合的聚合。
ROLLUP 生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合。
再看看對(duì)grouping的解釋：
當(dāng)行由 CUBE 或 ROLLUP 運(yùn)算符添加時(shí)，該函數(shù)將導(dǎo)致附加列的輸出值為 1；當(dāng)行不由 CUBE 或 ROLLUP 運(yùn)算符添加時(shí)，該函數(shù)將導(dǎo)致附加列的輸出值為 0。
僅在與包含 CUBE 或 ROLLUP 運(yùn)算符的 GROUP BY 子句相關(guān)聯(lián)的選擇列表中才允許分組。
當(dāng)看到以上的解釋肯定非常的模糊，不知所云和不知道該怎樣用，下面通過實(shí)例操作來體驗(yàn)一下：
--先建表（dbo.PeopleInfo）：
CREATE?TABLE?[dbo].[PeopleInfo](
??[id]?[int]?IDENTITY(1,1)?NOT?NULL,
??[name]?[nchar](10)?COLLATE?Chinese_PRC_CI_AS?NULL,
??[numb]?[nchar](10)?COLLATE?Chinese_PRC_CI_AS?NOT?NULL,
??[phone]?[nchar](10)?COLLATE?Chinese_PRC_CI_AS?NULL,
??[FenShu]?[int]?NULL
)?ON?[PRIMARY]
--向表插入數(shù)據(jù)：
insert?into?peopleinfo([name],numb,phone,fenshu)?values?('李歡','3223','1365255',80)
insert?into?peopleinfo([name],numb,phone,fenshu)?values?('李歡','322123','1',90)
insert?into?peopleinfo([name],numb,phone,fenshu)?values?('李名','3213112352','13152',56)
insert?into?peopleinfo([name],numb,phone,fenshu)?values?('李名','32132312','13342563',60)
insert?into?peopleinfo([name],numb,phone,fenshu)?values?('王華','3223','1365255',80)
--查詢出插入的全部數(shù)據(jù)：
select?*?from?dbo.PeopleInfo結(jié)果如圖：
操作一：1、查詢所有數(shù)據(jù)；2、用group by 查詢所有數(shù)據(jù)；3、用with cube。
select?*?from?dbo.PeopleInfo??--1,?查詢所有數(shù)據(jù)； select?[name],numb,sum(fenshu)?from?dbo.PeopleInfo?group?by?[name],numb??--2,用group?by?查詢所有數(shù)據(jù)； select?[name],numb,sum(fenshu)?from?dbo.PeopleInfo?group?by?[name],numb?with?cube??--3,用with?cube。這三種情況的比較
這三種情況的比較結(jié)果如圖：
結(jié)果分析：
用第三種(用with cube)為什么會(huì)多出來有null的字段值呢？通過分析圖上的值得組合會(huì)發(fā)現(xiàn)是怎么回事兒了，以第三條數(shù)據(jù)（李歡，null，170）為例：它只是把姓名是【李歡】的分為了一組，而沒有考慮【numb】，所以有多出來了第三條數(shù)據(jù)，也說明了170是怎么來的。其他的也是這樣。再回顧一下幫助文檔的解釋：CUBE 生成的結(jié)果集顯示了所選列中值的所有組合的聚合，?發(fā)現(xiàn)明了了許多。
操作二：1、用with cube；2、用with rollup
select?[name],numb,sum(fenshu)?from?dbo.PeopleInfo?group?by?[name],numb?with?cube??--用with?cube。 select?[name],numb,sum(fenshu)?from?dbo.PeopleInfo?group?by?[name],numb?with?rollup??--用with?rollup。
結(jié)果如圖：
結(jié)果分析：
為什么with cube 比 with rollup多出來一部分呢？原來它沒有顯示，以【numb】分組而不考慮【name】的數(shù)據(jù)情況。再回顧一下幫助文檔的解釋：ROLLUP 生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合，那這個(gè)【某一層次】又是以什么為標(biāo)準(zhǔn)的呢？我的猜想是：距離group up最近的字段必須考慮在分組內(nèi)。
證明猜想實(shí)例：
操作：用兩個(gè)group up 交換字段位置的sql語句和一個(gè)在group up 后面增加一個(gè)字段的sql語句進(jìn)行比較：
SQL語句如下：
select?[name],numb?from?dbo.PeopleInfo?group?by?[name],numb?with?rollup select?[name],numb?from?dbo.PeopleInfo?group?by?numb,[name]?with?rollup select?[name],numb,phone?from?dbo.PeopleInfo?group?by?[name],numb,phone?with?rollup
結(jié)果如圖：
通過結(jié)果圖的比較發(fā)現(xiàn)猜想是正確的。
---------------------------------------------------grouping-------------------------------------------------
現(xiàn)在來看看grouping的實(shí)例：
SQL語句看看與with rollup的結(jié)合（與with cube的結(jié)合是一樣的）：
select?[name],numb,grouping(numb)?from?dbo.PeopleInfo?group?by?[name],numb?with?rollup
結(jié)果如圖：
結(jié)果分析：
結(jié)合幫助文檔的解釋：當(dāng)行由 CUBE 或 ROLLUP 運(yùn)算符添加時(shí)，該函數(shù)將導(dǎo)致附加列的輸出值為 1；當(dāng)行不由 CUBE 或 ROLLUP 運(yùn)算符添加時(shí)，該函數(shù)將導(dǎo)致附加列的輸出值為 0。? 很容易理解再此就不多解釋了。
原文：http://www.2cto.com/database/201304/206327.html
SELECT??Groups?=?CASE?WHEN?GROUPING(Color)?=?0?THEN?Groups ??????????????????????WHEN?GROUPING(Groups)?=?1?THEN?'總計(jì)' ??????????????????????ELSE?'' ?????????????????END?, ????????Item?=?CASE?WHEN?GROUPING(Color)?=?0?THEN?Item ????????????????????WHEN?GROUPING(Item)?=?1?THEN?Groups?+?'?合計(jì)' ????????????????????ELSE?'' ???????????????END?, ????????Color?=?CASE?WHEN?GROUPING(Color)?=?0?THEN?Color ?????????????????????WHEN?GROUPING(Color)?=?1?THEN?Item?+?'?小計(jì)' ?????????????????????ELSE?'' ????????????????END?, ????????Quantity?=?SUM(Quantity) FROM????@t GROUP?BY?Groups?, ????????Item?, ????????Color ????????WITH?ROLLUP ORDER?BY?GROUPING(Groups)?, ????????CASE?WHEN?GROUPING(Groups)?=?0?THEN?Groups ????????END?DESC?, ????????GROUPING(Item)?, ????????CASE?WHEN?GROUPING(Item)?=?0?THEN?Item ????????END?DESC?, ????????GROUPING(Color)?, ????????CASE?WHEN?GROUPING(Color)?=?0?THEN?Color ????????END?DESC;
下載文檔
