SQL SERVER中的union，cube，rollup和cumpute運算子

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查詢中使用，它們不影響最終結果

         --例如:

        select name as 姓名,class as 班級,grade as 年級

              into #students

              from stud87

              union all

         select * from stud88

              union all

         select * from stud89

              order by 年級

         將3個班級的合并結果（按grade排序）插入到暫存資料表 #students裡面

         用 CUBE 摘要資料

         CUBE 運算子產生的結果集是Cube。Cube是事實資料的擴充，事實資料即記錄個別事件的資料。擴充建立在使用者打算分析的列上。這些列被稱為維。Cube是一個結果集，其中包含了各維度所有可能組合的交叉表格。

         CUBE 運算子在 SELECT 語句的 GROUP BY 子句中指定。該語句的挑選清單應包含維度列和彙總函式運算式。GROUP BY 應指定維度列和關鍵字 WITH CUBE。結果集將包含維度列中各值的所有可能組合，以及與這些維度值組合相匹配的基礎行中的彙總值。

         例如，一個簡單的表 Inventory 中包含：

         Item                  Color                 Quantity                   
         -------------------- -------------------- ----------
         Table                 Blue                  124                        
         Table                 Red                   223                        
         Chair                 Blue                  101                        
         Chair                 Red                   210                        

         下列查詢返回的結果集中，將包含 Item 和 Color 的所有可能組合的 Quantity 小計：

         SELECT Item, Color, SUM(Quantity) AS QtySum
         FROM Inventory
         GROUP BY Item, Color WITH CUBE

         下面是結果集：

         Item                  Color                 QtySum                     
         -------------------- -------------------- ---------
         Chair                 Blue                  101.00                     
         Chair                 Red                   210.00                     
         Chair                 (null)                311.00                     
         Table                 Blue                  124.00                     
         Table                 Red                   223.00                     
         Table                 (null)                347.00                     
         (null)                (null)                658.00                     
         (null)                Blue                  225.00                     
         (null)                Red                   433.00                     

         我們著重考查下列各行：

         Chair                 (null)                311.00                     

         這一行報告了 Item 維度中值為 Chair 的所有行的小計。對 Color 維度返回了 NULL 值，表示該行所報告的彙總包括 Color 維度為任意值的行。

         Table                 (null)                347.00                     

         這一行類似，但報告的是 Item 維度中值為 Table 的所有行的小計。

         (null)                (null)                658.00                     

         這一行報告了Cube的總計。Item 和 Color 維度值都是 NULL，表示兩個維度中的所有值都匯總在該行中。

         (null)                Blue                  225.00                     
         (null)                Red                   433.00                     

         這兩行報告了 Color 維度小計。兩行中的 Item 維度值都是 NULL，表示彙總資料來自 Item 維度為任意值的行。

         使用 GROUPING 區分空值
         CUBE 操作所產生的空值帶來一個問題：如何區分 CUBE 操作所產生的 NULL 值和從實際資料中返回的 NULL 值？這個問題可用 GROUPING 函數解決。如果列中的值來自事實資料，則 GROUPING 函數返回 0；如果列中的值是 CUBE 操作所產生的 NULL，則返回 1。在 CUBE 操作中，所產生的 NULL 代表全體值。可將 SELECT 語句寫成使用 GROUPING 函數將所產生的 NULL 替換為字串 ALL。因為事實資料中的 NULL 表明資料值未知，所以 SELECT 語句還可解碼為返回字串 UNKNOWN 替代來自事實資料的 NULL。例如：

         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

         包含帶有許多維度 CUBE 的 SELECT 語句可能產生很大的結果集，因為這些語句會為所有維度中值的所有組合產生行。這些大結果集包含的資料可能過多而不易於閱讀和理解。這個問題有一種解決辦法是將 SELECT 語句放在視圖中：

        CREATE VIEW InvCube AS
         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 CUBE

         然後即可用該視圖來只查詢您感興趣的維度值：

        SELECT *
         FROM InvCube
         WHERE Item = ''Chair''
          AND Color = ''ALL''

         Item                  Color                 QtySum                     
         -------------------- -------------------- ---------
         Chair                 ALL                   311.00                     

         (1 row(s) affected)

         用 ROLLUP 摘要資料在產生包含小計和合計的報表時，ROLLUP 運算子很有用。ROLLUP 運算子產生的結果集類似於 CUBE 運算子所產生的結果集。有關更多資訊，請參見用 CUBE 摘要資料。 CUBE 和 ROLLUP 之間的區別在於： CUBE 產生的結果集顯示了所選列中值的所有組合的彙總。ROLLUP 產生的結果集顯示了所選列中值的某一階層的彙總。 例如，簡單表 Inventory 中包含：Item                                  Color                 Quantity                   
         -------------------- -------------------- ------
         Table                 Blue                  124                        
         Table                 Red                   223                        
         Chair                 Blue                  101                        
         Chair                 Red                   210                        
         下列查詢將產生小計報表：
               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 關鍵字更改為 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 產生的更為高效。

         用 COMPUTE 和 COMPUTE BY 摘要資料
         提供 COMPUTE 和 COMPUTE BY 是為了向後相容。請改為使用下列組件：

         Microsoft SQL Server 2000 Analysis Services 和用於 Analysis Services 的 OLE DB 或 Microsoft ActiveX 資料對象（多維）(ADO MD) 一起使用。有關更多資訊，請參見 Microsoft SQL Server 2000 Analysis Services。
         COMPUTE BY 子句使您得以用同一 SELECT 語句既查看明細行，又查看合計列。可以計運算元組的匯總值，也可以計算整個結果集的匯總值。

         COMPUTE 子句需要下列資訊：

         可選的 BY 關鍵字，該關鍵字可按對一列計算指定的行彙總。

         資料列彙總函式名稱；例如，SUM、AVG、MIN、MAX 或 COUNT。

         要對其執行資料列彙總函式的列。
         COMPUTE 產生的結果集
         COMPUTE 所產生的匯總值在查詢結果中顯示為分離的結果集。包括 COMPUTE 子句的查詢的結果類似於控制中斷報告，即匯總值由指定的組（或稱中斷）控制的報表。可以為各組產生匯總值，也可以對同一組計算多個彙總函式。

         當 COMPUTE 帶有可選的 BY 子句時，符合 SELECT 條件的每個組都有兩個結果集：

         每個組的第一個結果集是明細行集，其中包含該組的挑選清單資訊。

         每個組的第二個結果集有一行，其中包含該組的 COMPUTE 子句中所指定的彙總函式的小計。
當 COMPUTE 不帶可選的 BY 子句時，SELECT 語句有兩個結果集：

         每個組的第一個結果集是包含挑選清單資訊的所有明細行。

         第二個結果集有一行，其中包含 COMPUTE 子句中所指定的彙總函式的合計。
COMPUTE 用法樣本
         下列 SELECT 語句使用簡單 COMPUTE 子句產生 titles 表中 price 及 advance 的求和總計：

USE pubs
SELECT type, price, advance
FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance)

下列查詢在 COMPUTE 子句中加入可選的 BY 關鍵字，以產生每個組的小計：

         USE pubs
         SELECT type, price, advance
         FROM titles
         ORDER BY type
         COMPUTE SUM(price), SUM(advance) BY type

         此 SELECT 語句的結果用 12 個結果集返回，六個組中的每個組都有兩個結果集。每個組的第一個結果集是一個行集，其中包含挑選清單中所請求的資訊。每個組的第二個結果集包含 COMPUTE 子句中兩個 SUM 函數的小計。

         說明   一些工具 + 生產力（如 osql）顯示多個小計或合計摘要彙總的方式可能會使使用者以為每個小計都是結果集中的單獨一行。這是由於該工具 + 生產力設定輸出格式的方式；小計或合計彙總返回時單獨佔用一行。其它應用程式（如 SQL 查詢分析器）將多個彙總設定在同一行。

比較 COMPUTE 和 GROUP BY
         COMPUTE 和 GROUP BY 之間的區別匯總如下：

         GROUP BY 產生單個結果集。每個組都有一個只包含分組依據列和顯示該組子彙總的彙總函式的行。挑選清單只能包含分組依據列和彙總函式。

         COMPUTE 產生多個結果集。一類結果集包含每個組的明細行，其中包含挑選清單中的運算式。另一類結果集包含組的子彙總，或 SELECT 語句的總彙總。挑選清單可包含除分組依據列或彙總函式之外的其它運算式。彙總函式在 COMPUTE 子句中指定，而不是在挑選清單中。
         下列查詢使用 GROUP BY 和彙總函式；該查詢將返回一個結果集，其中每個組有一行，該行中包含該組的彙總小計：

        USE pubs
         SELECT type, SUM(price), SUM(advance)
         FROM titles
         GROUP BY type

         說明   在 COMPUTE 或 COMPUTE BY 子句中，不能包含 ntext、text 或 image 資料類型。