SQL Server GROUP BY中的WITH CUBE、WITH ROLLUP原理測驗及GROUPING應用

    前幾天，看到一個群友用WITH ROLLUP運算子，由于自個兒沒用過，看到概念及結果都云里霧里的，所以突然來了興趣對生成結果測了一番，

    一、概念：

    WITH CUBE：生成的結果集顯示了所選列中值的所有組合的聚合，

    WITH ROLLUP：生成的結果集顯示了所選列中值的某一層次結構的聚合，

    GROUPING：當行由 WITH CUBE或WITH ROLLUP運算子添加時，該函式將導致附加列的輸出值為 1；當行不由 CUBE 或 ROLLUP 運算子添加時，該函式將導致附加列的輸出值為 0，僅在與包含 CUBE 或 ROLLUP 運算子的 GROUP BY 子句相關聯的選擇串列中才允許分組，

    二、測驗：

    1、建立臨時表

CREATE TABLE #T0
(
    [GRADE] [VARCHAR](50) NULL,     --年級
    [CLASS] [VARCHAR](50) NULL,     --班級
    [NAME] [VARCHAR](50) NULL,      --姓名
    [COURSE] [VARCHAR](50) NULL,    --學科
    [RESULT] [NUMERIC](8,2) NULL    --成績
)

CREATE TABLE #T1
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,    --序號
    [GRADE] [VARCHAR](50) NULL,           --年級
    [CLASS] [VARCHAR](50) NULL,           --班級
    [NAME] [VARCHAR](50) NULL,            --姓名
    [COURSE] [VARCHAR](50) NULL,          --學科
    [RESULT] [NUMERIC](8,2) NULL          --成績
)

CREATE TABLE #T2
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,    --序號
    [GRADE] [VARCHAR](50) NULL,           --年級
    [CLASS] [VARCHAR](50) NULL,           --班級
    [NAME] [VARCHAR](50) NULL,            --姓名
    [COURSE] [VARCHAR](50) NULL,          --學科
    [RESULT] [NUMERIC](8,2) NULL          --成績
)

     2、插入測驗資料

INSERT INTO #T0 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT '2019','CLASS1','9A01','C#',100
UNION
SELECT '2019','CLASS1','9A02','C#',100
UNION
SELECT '2019','CLASS2','9B01','C#',100
UNION
SELECT '2019','CLASS2','9B02','C#',100
UNION
SELECT '2018','CLASS1','8A01','JAVA',100
UNION
SELECT '2018','CLASS1','8A02','JAVA',100
UNION
SELECT '2018','CLASS2','8B01','JAVA',100
UNION
SELECT '2018','CLASS2','8B02','JAVA',100

    查詢T0表結果：

    3、GROUP BY

    拋磚引玉，看看常用的GROUP BY排序：默認以SELECT欄位順序(GRADE->CLASS->NAME->COURSE)進行排序，以下兩種查詢結果是一樣的，

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE
ORDER BY GRADE,CLASS,NAME,COURSE

    4、WITH CUBE

    原理1：以GROUP BY欄位依次賦以NULL值進行分組聚合，

    原理2：第1個欄位(即GRADE欄位)生成結果：除原始資料外，以第1個欄位固定賦以NULL值，然后其它欄位依次賦以NULL值進行分組聚合，結果由右往左進行排序，

    下面開始測第1個欄位的結果是怎么來的：

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUP BY GRADE,CLASS,NAME,COURSE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT 'ZZ' GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUP BY CLASS,NAME,COURSE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT 'ZZ' GRADE,'ZZ' CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUP BY NAME,COURSE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT 'ZZ' GRADE,'ZZ' CLASS,'ZZ' NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUP BY COURSE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT 'ZZ' GRADE,'ZZ' CLASS,'ZZ' NAME,'ZZ' COURSE,SUM(RESULT) RESULT 
FROM #T0

--第1個欄位結果排序由右往左
INSERT INTO #T2 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,RESULT FROM #T1 WHERE ID BETWEEN 1 AND 27 ORDER BY COURSE,NAME,CLASS,GRADE

UPDATE #T2 SET GRADE=NULL WHERE GRADE='ZZ'
UPDATE #T2 SET CLASS=NULL WHERE CLASS='ZZ'
UPDATE #T2 SET NAME=NULL WHERE NAME='ZZ'
UPDATE #T2 SET COURSE=NULL WHERE COURSE='ZZ'

    WITH CUBE的結果：

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE
WITH CUBE

    自已測驗的結果：

SELECT * FROM #T2

    結果與上面一致，

    其它欄位優先跟哪個欄位組合、最終怎樣排序？呃，測過，沒搞清楚……

    5、WITH ROLLUP

    原理1：除原始資料外，以GROUP BY最后1個欄位(即COURSE欄位)固定賦以NULL值，然后其它欄位依次賦以NULL值進行分組聚合，結果由左往右進行排序，

    這個跟WITH CUBE的第1個欄位非常相象：一個是第1個欄位，一個是最后1個欄位；一個結果是由右往左排序，一個結果是由左往右排序，

    下面開始測結果是怎么來的：

TRUNCATE TABLE #T1
TRUNCATE TABLE #T2

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT 
FROM #T0 
GROUP BY GRADE,CLASS,NAME,COURSE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,'ZZ' COURSE,SUM(RESULT) RESULT 
FROM #T0 
WHERE NOT EXISTS (SELECT 1 FROM #T1 WHERE GRADE=#T0.GRADE AND CLASS=#T0.GRADE AND NAME=#T0.NAME AND COURSE='ZZ')
GROUP BY GRADE,CLASS,NAME

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,'ZZ' NAME,'ZZ' COURSE,SUM(RESULT) RESULT 
FROM #T0 
WHERE NOT EXISTS (SELECT 1 FROM #T1 WHERE GRADE=#T0.GRADE AND CLASS=#T0.CLASS AND NAME='ZZ' AND COURSE='ZZ')
GROUP BY GRADE,CLASS

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,'ZZ' CLASS,'ZZ' NAME,'ZZ' COURSE,SUM(RESULT) RESULT 
FROM #T0 
WHERE NOT EXISTS (SELECT 1 FROM #T1 WHERE GRADE=#T0.GRADE AND CLASS='ZZ' AND NAME='ZZ' AND COURSE='ZZ')
GROUP BY GRADE

INSERT INTO #T1 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT 'ZZ' GRADE,'ZZ' CLASS,'ZZ' NAME,'ZZ' COURSE,SUM(RESULT) RESULT 
FROM #T0 

--結果排序由左往右
INSERT INTO #T2 (GRADE,CLASS,NAME,COURSE,RESULT)
SELECT GRADE,CLASS,NAME,COURSE,RESULT FROM #T1 ORDER BY GRADE,CLASS,NAME,COURSE

UPDATE #T2 SET GRADE=NULL WHERE GRADE='ZZ'
UPDATE #T2 SET CLASS=NULL WHERE CLASS='ZZ'
UPDATE #T2 SET NAME=NULL WHERE NAME='ZZ'
UPDATE #T2 SET COURSE=NULL WHERE COURSE='ZZ'

    WITH ROLLUP的結果：

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE
WITH ROLLUP

    自己測驗的結果：

SELECT * FROM #T2

    結果與上面一致，

    6、GROUPING

    這個就比較容易理解了，WITH CUBE與WITH ROLLUP用法一樣，先看結果：

SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT,GROUPING(COURSE) [GROUPING]
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE
WITH ROLLUP

    上面GROUPING的是COURSE欄位，有NULL值就是WITH ROLLUP額外添加的，GROUPING結果值為1，

    有了GROUPING，那做小計、總計就方便了，

SELECT 
    GRADE,
    CASE WHEN GROUPING(GRADE)=1 AND GROUPING(CLASS)=1 THEN '總計' WHEN GROUPING(GRADE)=0 AND GROUPING(CLASS)=1 THEN '小計' ELSE CLASS END CLASS,
    NAME,COURSE,SUM(RESULT) RESULT
FROM #T0
GROUP BY GRADE,CLASS,NAME,COURSE
WITH ROLLUP

     好了，原理測驗及應用就到這里結束了，

標籤：其他

上一篇：表單生成器(Form Builder)之表單資料存盤結構mongodb篇

下一篇：記一次ES查詢資料突然變為空的問題