Instructions for using the union,cube,rollup,cumpute operator in SQL Server _mssql

/*
The--1 UNION operator combines the results of two or more queries into a single result set
There are two basic rules for the result set of queries with UNION combinations:
1. The number of columns in all queries must be the same as the order of the columns.
2. Data types must be compatible
The result set column name of the a.union is the same as the column name in the result set in the first SELECT statement, and the result sets column name of the other SELECT statement is ignored
B. By default, the UNION operator deletes duplicate rows from the result set. If you use the ALL keyword, the result set will contain all rows and do not delete duplicate rows
C.sql is the value of the statement containing the union operator from left to right, using parentheses to change the order of evaluation
-For example:
*/
SELECT * FROM TableA
UNION ALL
(
SELECT * FROM TableB
UNION ALL
SELECT * FROM TableC
)
/*
This allows the TableB and TableC to be merged before merging TableA
D. If you want to save the merged result set to a new datasheet, the into statement must be added to the first select
E. You can use the ORDER BY and COMPUTE clauses only in the last SELECT statement, which affects the sort and count totals of the final merged results
F.group by and HAVING clauses can be used in a single select query, and they do not affect the end result
*/
--2 Cube Summary Data
/*
The cube operator produces a result set that is a multidimensional dataset. A cube is an extension of the fact data, which is the data that records individual events.
The extension is based on the columns that the user intends to analyze. These columns are called dimensions. A cube is a result set that contains a crosstab table of all possible combinations of each dimension.
The CUBE operator is specified in the GROUP by clause of a SELECT statement. The select list of the statement should contain dimension columns and aggregate function expressions.
GROUP by should specify dimension columns and keywords with CUBE. The result set will contain all possible combinations of the values in the Dimension column and the aggregate values in the underlying rows that match the combination of these dimension values.
*/
--The following query returns a result set that will contain Quantity subtotals for all possible combinations of Item and Color:
-->title: Generating test data
-->author:wufeng4552
-->date:2009-09-10 14:36:20
If not object_id (' Tempdb ... #t ') is null
drop table #t
Go
Create table #t ([Item] nvarchar (5), [Color] nvarchar (4), [Quantity] int)
Insert #t
Select n ' Table ', n ' Blue ', 124 UNION ALL
Select n ' Table ', n ' Red ', 223 union ALL
Select n ' Chair ', n ' Blue ', UNION ALL
Select n ' Chair ', n ' Red ', 210
Go
Select [Item],
[Color],
SUM ([Quantity]) [Quantity]
From #t group by [Item],[color] with Cube
/*
Item Color Quantity
----- ----- -----------
Chair Blue 101
Chair Red 210
Chair NULL 311
Table Blue 124
Table Red 223
Table NULL 347
NULL NULL 658
NULL Blue 225
NULL Red 433
*/
The null value generated by the/*cube operation poses a problem: how to distinguish between null values generated by the cube operation and null values returned from the actual data?
This problem can be solved using the GROUPING function.
If the value in the column comes from the fact data, the GROUPING function returns 0, or 1 if the value in the column is NULL generated by the CUBE operation.
In the CUBE operation, the generated NULL represents the whole value. You can write a SELECT statement using the GROUPING function to replace the generated NULL with the string all.
Because null in fact data indicates that the data value is unknown, the SELECT statement can also be decoded to return a string UNKNOWN to replace NULL from the fact data.
For example:
*/
-->title: Generating test data
-->author:wufeng4552
-->date:2009-09-10 14:36:20
If not object_id (' Tempdb ... #t ') is null
drop table #t
Go
Create table #t ([Item] nvarchar (5), [Color] nvarchar (4), [Quantity] int)
Insert #t
Select n ' Table ', n ' Blue ', 124 UNION ALL
Select n ' Table ', n ' Red ', 223 union ALL
Select n ' Chair ', n ' Blue ', UNION ALL
Select n ' Chair ', n ' Red ', 210
Go
SELECT [Item]=case when grouping ([item]) =1 then ' any ' else isnull (item, ' UNKNOWN ') end,
[Color]=case when grouping ([color]) =1 then ' any ' else isnull ([color], ' UNKNOWN ') end,
SUM ([Quantity]) [Quantity]
From #t group by [Item],[color] with Cube
/*
Item Color Quantity
----- ----- -----------
Chair Blue 101
Chair Red 210
Chair All 311
Table Blue 124
Table Red 223
Table All 347
All 658
All Blue 225
All Red 433
(9 data columns are impacted)
*/
/*
A SELECT statement that contains a cube with many dimensions can produce a large result set, because these statements will be rows for all combinations of values in all dimensions.
These large result sets may contain too much data to be easily read and understood. One solution to this problem is to put the SELECT statement in the view:
*/
CREATE VIEW View_cube
As
SELECT [Item]=case when grouping ([item]) =1 then ' any ' else isnull (item, ' UNKNOWN ') end,
[Color]=case when grouping ([color]) =1 then ' any ' else isnull ([color], ' UNKNOWN ') end,
SUM ([Quantity]) [Quantity]
From TB group by [Item],[color] with cube--The view cannot use the time table, so change the
--You can then use this view to query only the dimension values that you are interested in:
SELECT *
From Invcube
WHERE Item = ' Chair ' and Color = ' all '
/*
Item Color Qtysum
-------------------- -------------------- ---------
Chair all 311.00
*/
--3 ROLLUP Summary Data
/*
The ROLLUP operator is useful for summarizing data with ROLLUP when generating reports that contain subtotals and totals.
The ROLLUP operator produces a result set similar to the result set generated by the cube operator.
The difference between cube and ROLLUP is that cube generates a result set that shows aggregates 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. For example, a simple table #t
Contains: Item Color Quantity
*/
SELECT [Item]=case when grouping ([item]) =1 then ' any ' else isnull (item, ' UNKNOWN ') end,
[Color]=case when grouping ([color]) =1 then ' any ' else isnull ([color], ' UNKNOWN ') end,
SUM ([Quantity]) [Quantity]
From #t group by [Item],[color] with rollup
/*
Item Color Quantity
----- ----- -----------
Chair Blue 101
Chair Red 210
Chair All 311
Table Blue 124
Table Red 223
Table All 347
All 658
(7 data columns are impacted)
*/
/*
If the ROLLUP keyword in the query is changed to cube, the cube result set is the same as the result, except that the following two rows are returned at the end of the result set: All Blue 225.00
All Red 433.00
The CUBE operation is a possible combination of the values in Item and Color.
For example, the CUBE not only reports all possible combinations of Color values that are combined with the Item value Chair (red, blue, and Red + blue),
and report all possible combinations of Item values that are combined with the Color value Red (Chair, table, and Chair + table).
For each value in the column to the right of the GROUP by clause, the ROLLUP operation does not report all possible combinations of values in the left column (or column on the left). For example
ROLLUP does not report all possible combinations of Item values for each Color value.
The result set of the ROLLUP operation has functionality similar to the result set returned by the COMPUTE by; however, ROLLUP has the following advantages: ROLLUP Returns a single result set, COMPUTE by returns multiple result sets, and multiple result assemblies increase the complexity of the application code.
ROLLUP can be used in server cursors; COMPUTE by is not available.
Sometimes, the query optimizer generates more efficient execution plans for ROLLUP than for COMPUTE by.
*/