作者　: hongyuan
標(biāo)題　: 統(tǒng)計(jì)報(bào)表(用rollup 匯總數(shù)據(jù))
關(guān)鍵字:
分類　: sql server 2000
密級(jí)　: 私有
[align=right] [/align]

統(tǒng)計(jì)報(bào)表(用rollup 匯總數(shù)據(jù))
原貼：http://community.csdn.net/expert/topic/4313/4313978.xml?temp=.691601


表inventory
item color quantity
-------------------- -------------------- --------------------------
table blue 124
table red 223
chair blue 101
chair red 210

要得到下面結(jié)果:

item color qtysum
-------------------- -------------------- --------------------------
chair blue 101.00
chair red 210.00
chair小計(jì) 311.00
table blue 124.00
table red 223.00
table小計(jì) 347.00
總計(jì) 658.00


---該問題是一個(gè)典型的使用rollup生成結(jié)合的例子,聯(lián)機(jī)幫助也有相關(guān)介紹！


-測(cè)試環(huán)境
declare @inventory table (item varchar(20),color varchar(20),quantity money)
insert into @inventory select 'table','blue',124
insert into @inventory select 'table','red' ,223
insert into @inventory select 'chair','blue',101
insert into @inventory select 'chair','red' ,210

--查詢
select case when (grouping(item) = 1) then '總計(jì)'
when (grouping(color) = 1) then item+'小計(jì)'
else isnull(item, 'unknown')
end as item,
case when (grouping(color) = 1) then ''
else isnull(color, 'unknown')
end as color,
sum(quantity) as qtysum
from @inventory
group by item, color with rollup

--結(jié)果
item color qtysum
------------------------ -------------------- ---------------------
chair blue 101.0000
chair red 210.0000
chair小計(jì) 311.0000
table blue 124.0000
table red 223.0000
table小計(jì) 347.0000
總計(jì) 658.0000

（所影響的行數(shù)為 7 行）

[align=right]2005-10-10 9:44:39 [/align]


修改筆記
發(fā)表評(píng)語»»» 2005-10-10 10:01:01 grouping

grouping
是一個(gè)聚合函數(shù),它產(chǎn)生一個(gè)附加的列，當(dāng)用 cube 或 rollup 運(yùn)算符添加行時(shí)，附加的列輸出值為1，當(dāng)所添加的行不是由 cube 或 rollup 產(chǎn)生時(shí)，附加列值為0。

僅在與包含 cube 或 rollup 運(yùn)算符的 group by 子句相聯(lián)系的選擇列表中才允許分組。

語法
grouping ( column_name )

參數(shù)
column_name

是 group by 子句中用于檢查 cube 或 rollup 空值的列。

返回類型
int

注釋
分組用于區(qū)分由 cube 和 rollup 返回的空值和標(biāo)準(zhǔn)的空值。作為cube 或 rollup 操作結(jié)果返回的 null 是 null 的特殊應(yīng)用。它在結(jié)果集內(nèi)作為列的占位符，意思是"全體"。

[align=right] [/align] 2005-10-10 10:02:45 用 rollup 匯總數(shù)據(jù)

用 rollup 匯總數(shù)據(jù)
在生成包含小計(jì)和合計(jì)的報(bào)表時(shí)，rollup 運(yùn)算符很有用。rollup 運(yùn)算符生成的結(jié)果集類似于 cube 運(yùn)算符所生成的結(jié)果集。有關(guān)更多信息，請(qǐng)參見用 cube 匯總數(shù)據(jù)。

cube 和 rollup 之間的區(qū)別在于：

cube 生成的結(jié)果集顯示了所選列中值的所有組合的聚合。


rollup 生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合。
例如，簡單表 inventory 中包含：

item color quantity
-------------------- -------------------- --------------------------
table blue 124
table red 223
chair blue 101
chair red 210

下列查詢將生成小計(jì)報(bào)表：

select case when (grouping(item) = 1) then 'all'
else isnull(item, 'unknown')
end as item,
case when (grouping(color) = 1) then 'all'
else isnull(color, 'unknown')
end as color,
sum(quantity) as qtysum
from inventory
group by item, color with rollup

item color qtysum
-------------------- -------------------- --------------------------
chair blue 101.00
chair red 210.00
chair all 311.00
table blue 124.00
table red 223.00
table all 347.00
all all 658.00

(7 row(s) affected)

如果查詢中的 rollup 關(guān)鍵字更改為 cube，那么 cube 結(jié)果集與上述結(jié)果相同，只是在結(jié)果集的末尾還會(huì)返回下列兩行：

all blue 225.00
all red 433.00

cube 操作為 item 和 color 中值的可能組合生成行。例如，cube 不僅報(bào)告與 item 值 chair 相組合的 color 值的所有可能組合（red、blue 和 red + blue），而且報(bào)告與 color 值 red 相組合的 item 值的所有可能組合（chair、table 和 chair + table）。

對(duì)于 group by 子句中右邊的列中的每個(gè)值，rollup 操作并不報(bào)告左邊一列（或左邊各列）中值的所有可能組合。例如，rollup 并不對(duì)每個(gè) color 值報(bào)告 item 值的所有可能組合。

rollup 操作的結(jié)果集具有類似于 compute by 所返回結(jié)果集的功能；然而，rollup 具有下列優(yōu)點(diǎn)：

rollup 返回單個(gè)結(jié)果集；compute by 返回多個(gè)結(jié)果集，而多個(gè)結(jié)果集會(huì)增加應(yīng)用程序代碼的復(fù)雜性。


rollup 可以在服務(wù)器游標(biāo)中使用；compute by 不可以。


有時(shí)，查詢優(yōu)化器為 rollup 生成的執(zhí)行計(jì)劃比為 compute by 生成的更為高效。