union,cube,rollup,cumpute運算符的使用技巧。
/*
–1 UNION 運算符是將兩個或更多查詢的結(jié)果組合為單個結(jié)果集
使用 UNION 組合查詢的結(jié)果集有兩個最基本的規(guī)則：
1。所有查詢中的列數(shù)和列的順序必須相同。
2。數(shù)據(jù)類型必須兼容
a.UNION的結(jié)果集列名與第一個select語句中的結(jié)果集中的列名相同，其他select語句的結(jié)果集列名被忽略
b.默認(rèn)情況下，UNION 運算符是從結(jié)果集中刪除重復(fù)行。如果使用all關(guān)鍵字，那么結(jié)果集將包含所有行并且不刪除重復(fù)行
c.sql是從左到右對包含UNION 運算符的語句進(jìn)行取值，使用括號可以改變求值順序
–例如:
*/
select * from tablea
union all
(
select * from tableb
union all
select * from tablec
)
/*
這樣就可以先對tableb和tablec合并，再合并tablea
d.如果要將合并后的結(jié)果集保存到一個新數(shù)據(jù)表中，那么into語句必須加入到第一條select中
e.只可以在最后一條select語句中使用 order by 和 compute 子句，這樣影響到最終合并結(jié)果的排序和計數(shù)匯總
f.group by 和 having 子句可以在單獨一個select查詢中使用，它們不影響最終結(jié)果
*/
–2 CUBE 匯總數(shù)據(jù)
/*
CUBE 運算符生成的結(jié)果集是多維數(shù)據(jù)集。多維數(shù)據(jù)集是事實數(shù)據(jù)的擴展，事實數(shù)據(jù)即記錄個別事件的數(shù)據(jù)。
擴展建立在用戶打算分析的列上。這些列被稱為維。多維數(shù)據(jù)集是一個結(jié)果集，其中包含了各維度的所有可能組合的交叉表格。
CUBE 運算符在 SELECT 語句的 GROUP BY 子句中指定。該語句的選擇列表應(yīng)包含維度列和聚合函數(shù)表達(dá)式。
GROUP BY 應(yīng)指定維度列和關(guān)鍵字 WITH CUBE。結(jié)果集將包含維度列中各值的所有可能組合，以及與這些維度值組合相匹配的基礎(chǔ)行中的聚合值。
*/
–下列查詢返回的結(jié)果集中，將包含 Item 和 Color 的所有可能組合的 Quantity 小計：
–>Title:生成測試數(shù)據(jù)
–>Author:wufeng4552
–>Date :2009-09-10 14:36:20
if not object_id(‘Tempdb..#t’) is null
drop table #t
Go
Create table #t([Item] nvarchar(5),[Color] nvarchar(4),[Quantity] int)
Insert #t
select N’Table’,N’Blue’,124 union all
select N’Table’,N’Red’,223 union all
select N’Chair’,N’Blue’,101 union all
select N’Chair’,N’Red’,210
Go
select [Item],
[Color],
sum([Quantity])[Quantity]
from #t group by [Item],[Color] with cube
/*
Item Color Quantity
—– —– ———–
Chair Blue 101
Chair Red 210
Chair NULL 311
Table Blue 124
Table Red 223
Table NULL 347
NULL NULL 658
NULL Blue 225
NULL Red 433
*/
/*CUBE 操作所生成的空值帶來一個問題：如何區(qū)分 CUBE 操作所生成的 NULL 值和從實際數(shù)據(jù)中返回的 NULL 值？
這個問題可用 GROUPING 函數(shù)解決。
如果列中的值來自事實數(shù)據(jù)，則 GROUPING 函數(shù)返回 0；如果列中的值是 CUBE 操作所生成的 NULL，則返回 1。
在 CUBE 操作中，所生成的 NULL 代表全體值?？蓪?SELECT 語句寫成使用 GROUPING 函數(shù)將所生成的 NULL 替換為字符串 ALL。
因為事實數(shù)據(jù)中的 NULL 表明數(shù)據(jù)值未知，所以 SELECT 語句還可譯碼為返回字符串 UNKNOWN 替代來自事實數(shù)據(jù)的 NULL。
例如：
*/
–>Title:生成測試數(shù)據(jù)
–>Author:wufeng4552
–>Date :2009-09-10 14:36:20
if not object_id(‘Tempdb..#t’) is null
drop table #t
Go
Create table #t([Item] nvarchar(5),[Color] nvarchar(4),[Quantity] int)
Insert #t
select N’Table’,N’Blue’,124 union all
select N’Table’,N’Red’,223 union all
select N’Chair’,N’Blue’,101 union all
select N’Chair’,N’Red’,210
Go
select [Item]=case when grouping([Item])=1 then ‘ALL’ else isnull(Item, ‘UNKNOWN’)end,
[Color]=case when grouping([Color])=1 then ‘ALL’ else isnull([Color],’UNKNOWN’)end,
sum([Quantity])[Quantity]
from #t group by [Item],[Color] with cube
/*
Item Color Quantity
—– —– ———–
Chair Blue 101
Chair Red 210
Chair ALL 311
Table Blue 124
Table Red 223
Table ALL 347
ALL ALL 658
ALL Blue 225
ALL Red 433
(9 個資料列受到影響)
*/
/*
包含帶有許多維度的 CUBE 的 SELECT 語句可能生成很大的結(jié)果集，因為這些語句會為所有維度中值的所有組合生成行。
這些大結(jié)果集包含的數(shù)據(jù)可能過多而不易于閱讀和理解。這個問題有一種解決辦法是將 SELECT 語句放在視圖中：
*/
create view view_cube
as
select [Item]=case when grouping([Item])=1 then ‘ALL’ else isnull(Item, ‘UNKNOWN’)end,
[Color]=case when grouping([Color])=1 then ‘ALL’ else isnull([Color],’UNKNOWN’)end,
sum([Quantity])[Quantity]
from tb group by [Item],[Color] with cube –視圖中不能用臨時表,故改之
–然后即可用該視圖來只查詢您感興趣的維度值：
SELECT *
FROM InvCube
WHERE Item = ‘Chair’ AND Color = ‘ALL’
/*
Item Color QtySum
——————– ——————– ———
Chair ALL 311.00
*/
–3 ROLLUP 匯總數(shù)據(jù)
/*
用 ROLLUP 匯總數(shù)據(jù)在生成包含小計和合計的報表時，ROLLUP 運算符很有用。
ROLLUP 運算符生成的結(jié)果集類似于 CUBE 運算符所生成的結(jié)果集。
CUBE 和 ROLLUP 之間的區(qū)別在于： CUBE 生成的結(jié)果集顯示了所選列中值的所有組合的聚合。
ROLLUP 生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合。 例如，簡單表 #t
中包含：Item Color Quantity
*/
select [Item]=case when grouping([Item])=1 then ‘ALL’ else isnull(Item, ‘UNKNOWN’)end,
[Color]=case when grouping([Color])=1 then ‘ALL’ else isnull([Color],’UNKNOWN’)end,
sum([Quantity])[Quantity]
from #t group by [Item],[Color] with rollup
/*
Item Color Quantity
—– —– ———–
Chair Blue 101
Chair Red 210
Chair ALL 311
Table Blue 124
Table Red 223
Table ALL 347
ALL ALL 658
(7 個資料列受到影響)
*/
/*
如果查詢中的 ROLLUP 關(guān)鍵字更改為 CUBE，那么 CUBE 結(jié)果集與上述結(jié)果相同，只是在結(jié)果集的末尾還會返回下列兩行：ALL Blue 225.00
ALL Red 433.00
CUBE 操作為 Item 和 Color 中值的可能組合生成行。
例如，CUBE 不僅報告與 Item 值 Chair 相組合的 Color 值的所有可能組合（Red、Blue 和 Red + Blue），
而且報告與 Color 值 Red 相組合的 Item 值的所有可能組合（Chair、Table 和 Chair + Table）。
對于 GROUP BY 子句中右邊的列中的每個值，ROLLUP 操作并不報告左邊一列（或左邊各列）中值的所有可能組合。例如，
ROLLUP 并不對每個 Color 值報告 Item 值的所有可能組合。
ROLLUP 操作的結(jié)果集具有類似于 COMPUTE BY 所返回結(jié)果集的功能；然而，ROLLUP 具有下列優(yōu)點： ROLLUP 返回單個結(jié)果集；COMPUTE BY 返回多個結(jié)果集，而多個結(jié)果集會增加應(yīng)用程序代碼的復(fù)雜性。
ROLLUP 可以在服務(wù)器游標(biāo)中使用；COMPUTE BY 不可以。
有時，查詢優(yōu)化器為 ROLLUP 生成的執(zhí)行計劃比為 COMPUTE BY 生成的更為高效。
*/
