程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQLServer 數據庫的數據匯總完全解析(WITH ROLLUP)

SQLServer 數據庫的數據匯總完全解析(WITH ROLLUP)

編輯:關於SqlServer
乍一看,好像很容易,用group by好像能實現?但仔細研究下去,你又會覺得group by也是無能為力,總欠缺點什麼,無從下手。那麼,到底該如何做呢?別急,SQL Server早就幫我們做好了,下面,跟我來。

現有表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 運算符在 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
        {
           將編碼值用“總計:”替換,將倉庫值用""替換;
           將這一行的著色標示為淡綠色;
        }
        指針移到下一條;
      }
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved