SQL Server資料匯總完全解析

前言：

在論壇上經常看到有人問“如何?資料的分類匯總”，很多的人都是介紹這樣或那樣的控制項來實現，而沒有從關聯式資料庫語言（SQL）的本身來考慮實現方法。這裡，我就借一個執行個體來說明如何藉助SQL自身強大的功能來實現資料的分類匯總。

問題的提出：  現有表A，內容如下： 編碼   倉庫    數量 01      A       6 01      B       7 02      A       8 02      B       9 現在想按編碼查詢出這種格式: -------------------- 01      A       6 01      B       7 匯總小計：     13 02      A       8 02      B       9 匯總小計：     17 問：該如何?？         乍一看，好像很容易，用group by好像能實現？但仔細研究下去，你又會覺得group by也是無能為力，總欠缺點什麼，無從下手。那麼，到底該如何做呢？別急，SQL Server早就幫我們做好了，下面，跟我來。         首先，讓我們來看一段話： 在產生包含小計和合計的報表時，ROLLUP 運算子很有用。ROLLUP 運算子產生的結果集類似於 CUBE 運算子所產生的結果集。 ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ CUBE 運算子產生的結果集是Cube。Cube是事實資料的擴充，事實資料即記錄個別事件的資料。擴充建立在使用者打算分析的列上。這些列被稱為維。Cube是一個結果集，其中包含了各維度所有可能組合的交叉表格。 CUBE 運算子在 SELECT 語句的 GROUP BY 子句中指定。該語句的挑選清單應包含維度列和彙總函式運算式。GROUP BY 應指定維度列和關鍵字 WITH CUBE。結果集將包含維度列中各值的所有可能組合，以及與這些維度值組合相匹配的基礎行中的彙總值。 ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝  CUBE 和 ROLLUP 之間的區別在於：  CUBE 產生的結果集顯示了所選列中值的所有組合的彙總。 ROLLUP 產生的結果集顯示了所選列中值的某一階層的彙總。         看完以上的這段話，悟出了什麼沒有？如果沒有，那麼……嘿嘿，你的悟性還不夠喲，離“三花棸頂”還早著呢：）。接下來我們再看一段（注意喲，答案馬上就揭曉了）： SELECT 編碼, 倉庫, Sum(數量) as 數量 FROM A GROUP BY 編碼, 倉庫 WITH ROLLUP --關鍵就是後面的WITH ROLLUP --當然，你也可以用WITH CUBE，但是結果會有點不大一樣         可能看完上面這段你還是覺得“雲裡霧裡”，摸不著頭腦。實在不明白也沒關係，自己動手做。         首先：建一個上面所說的A表，輸入幾行資料；         接著：開啟你的SQL Server查詢分析器，連上包含你上面所建A表的伺服器，選擇包含該表的資料庫；         然後：Copy上面這段SQL 陳述式，Paste到查詢分析器中，按F5，怎麼樣？看到下面出來了什嗎？是不是和我下面的一樣？   編碼       倉庫    數量 01            A         　6 01            B       　 7 01　　      NULL　 13 02            A             8 02            B             9 02　　     NULL　　17 NULL       NULL　　30 --如果你用的是WITH CUBE，結果集的後面還會多出兩條（如果你也只是輸入樣本中的幾行資料的話）： NULL          A          14 NULL          B          16         咦！奇怪，結果中怎麼有那麼多“NULL”值？哈，別急，這幾行正是我們所要的摘要資料行，不難看出：         01　NULL　13正是對編碼為01的所有倉庫中的數量的匯總；02　NULL　17是對編碼為02的所有倉庫的數量的匯總； NULL　NULL　30是對所有資料行數量的匯總。         如何？答案出來了吧？是不是很簡單呢？當然，上面還有點美中不足，那就是有好多“NULL”的存在。如何去掉這些無意義的NULL呢？下面我們再進行最佳化。 1、用Grouping替換NULL值 SELECT CASE WHEN (GROUPING(編碼) = 1) THEN 'ALL'             ELSE ISNULL(編碼, 'UNKNOWN')        END AS 編碼,        CASE WHEN (GROUPING(倉庫) = 1) THEN 'ALL'             ELSE ISNULL(倉庫, 'UNKNOWN')        END AS 倉庫,        SUM(數量) AS 數量 FROM A GROUP BY 編碼, 倉庫 WITH ROLLUP --適當的運用Case函數         結果我這裡就不寫了，就是把上面的“NULL”值全部換成“ALL”字串   2、利用程式做進一步的最佳化 //通常為了顯示上的需要，我們必須對以上SQL語句產生的結果做一些最佳化，下面給出自然語言描述： WHILE（未到達最後一條記錄）｛ 　　IF 編碼值不為ALL而倉庫值為ALL 　　｛ 　　　　　將編碼值用“小計：”替換，將倉庫值用""替換; 　　　　　將這一行的顏色標示為灰色; 　　｝ 　　ELSE 編碼值為ALL倉庫值也為ALL 　　｛ 　　　　　將編碼值用“總計：”替換，將倉庫值用""替換; 　　　　　將這一行的著色標示為淡綠色; 　　｝ 　　指標移到下一條; } //當然，你盡可以發揮你的想象，把表格打扮得漂漂亮亮的，我就不再羅嗦了。

結束語：         通過上面的講述，不知道你明白了沒有，限於作者的文字表達能力，未解釋清楚之處還請見諒。