前几天，看到一个群友用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
好了，原理测试及应用就到这里结束了。
GROUP BY中的WITH CUBE、WITH ROLLUP、GROUPING测试
原文：https://www.cnblogs.com/atomy/p/11795921.html