A few days ago, I saw a group of friends using WITH ROLLUP. Because they didn't use it, I had no time to test it.
1. Concept:
WITH CUBE: the resulting set shows the aggregation of all combinations of values in the selected column.
WITH ROLLUP: the resulting set shows an aggregation of a hierarchy of values in the selected column.
Group: when a row is added by the WITH CUBE or WITH ROLLUP operator, this function will cause the output value of the additional column to be 1; when a row is not added by the CUBE or ROLLUP operator, this function will cause the output value of the additional column to be 0.
Grouping is only allowed in a selection list associated with a GROUP BY clause that contains a CUBE or ROLLUP operator.
II. Test:
1. Establish temporary table
CREATE TABLE #T0 ( [GRADE] [VARCHAR](50) NULL, --grade [CLASS] [VARCHAR](50) NULL, --class [NAME] [VARCHAR](50) NULL, --Full name [COURSE] [VARCHAR](50) NULL, --subject [RESULT] [NUMERIC](8,2) NULL --achievement ) CREATE TABLE #T1 ( [ID] [INT] IDENTITY(1,1) NOT NULL, --Serial number [GRADE] [VARCHAR](50) NULL, --grade [CLASS] [VARCHAR](50) NULL, --class [NAME] [VARCHAR](50) NULL, --Full name [COURSE] [VARCHAR](50) NULL, --subject [RESULT] [NUMERIC](8,2) NULL --achievement ) CREATE TABLE #T2 ( [ID] [INT] IDENTITY(1,1) NOT NULL, --Serial number [GRADE] [VARCHAR](50) NULL, --grade [CLASS] [VARCHAR](50) NULL, --class [NAME] [VARCHAR](50) NULL, --Full name [COURSE] [VARCHAR](50) NULL, --subject [RESULT] [NUMERIC](8,2) NULL --achievement )
2. Insert test data
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
Query T0 table results:
3,GROUP BY
Sort: sort by default in the order of SELECT fields. The following two types of query results are the same.
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
Principle 1: group aggregation is performed by assigning NULL values to GROUP BY fields in turn.
Principle 2: the first field is that all fields will be grouped and aggregated with NULL values, and the results will be sorted from right to left.
Start to test the result of the first field:
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 --The first field results are sorted from right to left 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'
Results of WITH CUBE:
SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT FROM #T0 GROUP BY GRADE,CLASS,NAME,COURSE WITH CUBE
Self test results:
SELECT * FROM #T2
The result is the same as above.
Which field is the priority of other fields? And how to sort them? Well, I did. I didn't find out
5,WITH ROLLUP
Principle 1: the last field of GROUP BY is assigned with NULL value for group aggregation.
Principle 2: the results are sorted from left to right.
This is very similar to the first field of WITH CUBE: one is the first field, the other is the last field; one result is sorted from right to left, and one result is sorted from left to right.
How do the test results come from
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 --Sort results left to right 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'
Results of WITH ROLLUP:
SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT FROM #T0 GROUP BY GRADE,CLASS,NAME,COURSE WITH ROLLUP
Self test results:
SELECT * FROM #T2
The result is the same as above.
6,GROUPING
This is easy to understand. The usage of WITH CUBE is the same as that of WITH ROLLUP. First look at the result:
SELECT GRADE,CLASS,NAME,COURSE,SUM(RESULT) RESULT,GROUPING(COURSE) [GROUPING] FROM #T0 GROUP BY GRADE,CLASS,NAME,COURSE WITH ROLLUP
The above group is the COURSE field. If there is a NULL value, it is added additionally WITH ROLLUP. The group result value is 1.
With GROUPING, it is convenient to make subtotal and total.
SELECT GRADE, CASE WHEN GROUPING(GRADE)=1 AND GROUPING(CLASS)=1 THEN 'Total' WHEN GROUPING(GRADE)=0 AND GROUPING(CLASS)=1 THEN 'Subtotal' ELSE CLASS END CLASS, NAME,COURSE,SUM(RESULT) RESULT FROM #T0 GROUP BY GRADE,CLASS,NAME,COURSE WITH ROLLUP
OK, that's the end of the test.