/*
--1 UNION 運算子是將兩個或更多查詢的結果組合為單個結果集
使用 UNION 組合查詢的結果集有兩個最基本的規則：
1。所有查詢中的列數和列的順序必須相同。
2。資料類型必須相容
a.UNION的結果集列名與第一個select語句中的結果集中的列名相同，其他select語句的結果集列名被忽略
b.預設情況下，UNION 運算子是從結果集中重複資料刪除行。如果使用all關鍵字，那麼結果集將包含所有行並且不重複資料刪除行
c.sql是從左至右對包含UNION 運算子的語句進行取值，使用括弧可以改變求值順序
--例如:
*/
select * from tablea
union all
(
select * from tableb
union all
select * from tablec
)
/*
這樣就可以先對tableb和tablec合并，再合并tablea
d.如果要將合并後的結果集儲存到一個新資料表中，那麼into語句必須加入到第一條select中
e.只可以在最後一條select語句中使用 order by 和 compute 子句，這樣影響到最終合并結果的排序和計數匯總
f.group by 和 having 子句可以在單獨一個select查詢中使用，它們不影響最終結果
*/
--2 CUBE 摘要資料
/*
CUBE 運算子產生的結果集是Cube。Cube是事實資料的擴充，事實資料即記錄個別事件的資料。
擴充建立在使用者打算分析的列上。這些列被稱為維。Cube是一個結果集，其中包含了各維度所有可能組合的交叉表格。
CUBE 運算子在 SELECT 語句的 GROUP BY 子句中指定。該語句的挑選清單應包含維度列和彙總函式運算式。
GROUP BY 應指定維度列和關鍵字 WITH CUBE。結果集將包含維度列中各值的所有可能組合，以及與這些維度值組合相匹配的基礎行中的彙總值。
*/
--下列查詢返回的結果集中，將包含 Item 和 Color 的所有可能組合的 Quantity 小計：
-->Title:產生測試數據
-->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 操作所產生的空值帶來一個問題：如何區分 CUBE 操作所產生的 NULL 值和從實際資料中返回的 NULL 值？
這個問題可用 GROUPING 函數解決。
如果列中的值來自事實資料，則 GROUPING 函數返回 0；如果列中的值是 CUBE 操作所產生的 NULL，則返回 1。
在 CUBE 操作中，所產生的 NULL 代表全體值。可將 SELECT 語句寫成使用 GROUPING 函數將所產生的 NULL 替換為字串 ALL。
因為事實資料中的 NULL 表明資料值未知，所以 SELECT 語句還可解碼為返回字串 UNKNOWN 替代來自事實資料的 NULL。
例如：
*/
-->Title:產生測試數據
-->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 語句可能產生很大的結果集，因為這些語句會為所有維度中值的所有組合產生行。
這些大結果集包含的資料可能過多而不易於閱讀和理解。這個問題有一種解決辦法是將 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 摘要資料
/*
用 ROLLUP 摘要資料在產生包含小計和合計的報表時，ROLLUP 運算子很有用。
ROLLUP 運算子產生的結果集類似於 CUBE 運算子所產生的結果集。
CUBE 和 ROLLUP 之間的區別在於： CUBE 產生的結果集顯示了所選列中值的所有組合的彙總。
ROLLUP 產生的結果集顯示了所選列中值的某一階層的彙總。 例如，簡單表 #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 關鍵字更改為 CUBE，那麼 CUBE 結果集與上述結果相同，只是在結果集的末尾還會返回下列兩行：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 操作的結果集具有類似於 COMPUTE BY 所返回結果集的功能；然而，ROLLUP 具有下列優點： ROLLUP 返回單個結果集；COMPUTE BY 返回多個結果集，而多個結果集會增加應用程式代碼的複雜性。
ROLLUP 可以在伺服器資料指標中使用；COMPUTE BY 不可以。
有時，查詢最佳化工具為 ROLLUP 產生的執行計畫比為 COMPUTE BY 產生的更為高效。
*/
