Application of "T-SQL Series" with ROLLUP, with CUBE, grouping statement

Original: Application of "T-SQL Series" with ROLLUP, with CUBE, grouping statement

The difference between CUBE and ROLLUP is that:
The result set generated by the cube operator is a cube. A cube is an extension of fact data, which is the data that records individual events. The extension is built on the columns that the user intends to analyze. These columns are called dimensions. A cube is a result set that contains cross tables of all possible combinations of dimensions.
The CUBE operator is specified in the GROUP by clause of the SELECT statement. The selection list for this statement should contain the dimension columns and aggregate function expressions. The GROUP by should specify the dimension column and the keyword with CUBE. The result set will contain all possible combinations of values in the dimension columns, as well as aggregated values in the underlying row that match the combination of these dimension values.

The result set generated by cube shows aggregations for all combinations of values in the selected column.
The result set generated by ROLLUP shows the aggregation of a hierarchy of values in the selected column.
GROUPING:
When a row is added with the cube or ROLLUP operator, the appended column output value is 1, and the appended column value is 0 when the row being added is not produced by cube or ROLLUP.

It is important to note that when using a GROUP by statement with the WITH ROLLUP clause, the result set can no longer be sorted by using the order BY statement, and if the order of the returned results is not satisfactory, it needs to be sorted in the program after the application obtains the results.
Example:

DECLARE @T TABLE(NameVARCHAR(Ten), PublishersVARCHAR(Ten), Price 1INT, Price 2INT    )INSERT  @T        SELECT  'a' ,                'Beijing' ,                 One ,                 A        UNION  All        SELECT  'a' ,                'Sichuan' ,                 A ,                 -        UNION  All        SELECT  'b' ,                'Sichuan' ,                 A ,                 at        UNION  All        SELECT  'b' ,                'Beijing' ,                Ten ,                 -        UNION  All        SELECT  'b' ,                'Kunming' ,                 - ,                 -SELECT  * from    @T       --With CUBESELECTname, publisher,SUM(Price 1) asPrice 1,SUM(Price 2) asPrice 2,GROUPINGName asCheck Name,GROUPINGPublishers asCheck publisher from    @TGROUP  byname, publisher withCUBE--With ROLLUPSELECTname, publisher,SUM(Price 1) asPrice 1,SUM(Price 2) asPrice 2 from    @TGROUP  byname, publisher withROLLUP--GROUPINGSELECT   Case  when(GROUPINGName= 1) Then 'Total'             ELSE ISNULLName'UNKNOWN')        END  asname, Case  when(GROUPINGPublishers= 1) Then ' Subtotal'             ELSE ISNULLPublishers'UNKNOWN')        END  asPublishers,SUM(Price 1) asPrice 1,SUM(Price 2) asPrice 2 from    @TGROUP  byname, publisher withROLLUP

With Cube result set:

With ROLLUP result set:

Grouping result set:

T-SQL series with ROLLUP, with CUBE, grouping statement application