8 Eighth chapter CTE recursion and Grouping summary Advanced section (cube)
This map is too troublesome ... Never mind
Union and other set operators: Union and so on the first SELECT of the listing as the entire result set, the only logical processing phase of the entire result set is the order by this means that only order by is the effect of the entire result set, the other operations are Uinon on both sides of the sub-collection. The EXCEPT operator also proposes duplicate values, and it considers that two null values are equal, while not EXISTS considers two null values to be unequal, the precedence of the set operator is INTERSECT maximum, and the others are the same
CTE recursion is a focal point, divided into a single anchor member, a single recursive member, multiple anchor points, multiple recursive members 1. Single anchor member, single recursive member
Todo2. Multiple anchor points, multiple recursive members
Todo
Note that with CUBE or with ROLLUP The result is the same when there is only one dimension to watch. The WITH cube or with ROLLUP statements in the example below are replaced by the cube (col1,col2) or ROLLUP (col1,col2) behind GROUP by, as this is more ISO-compliant
IF object_id (' dbo. Inventory ', ' U ') is not NULL DROP TABLE dbo. Inventory GO
CREATE TABLE Inventory (Item varchar (5), Color char (4), Qty int) INSERT into dbo. Inventory VALUES (' table ', ' Blue ', 2), (' Table ', ' Red ', ' 1 '), (' Table ', ' Blue ', ' 3 '), (' chair ', ' Blue ', ' 4 '), (' chair ', ' Red ', 6), (' chair ', ' Red ', 5)
Select Item,color,sum (Qty) as Sum_qty from dbo. Inventory GROUP by Item,color with Cube-the group by Item,color with Cube can also be replaced by the group by CUBE (Item,color) following the same
Select Item,color,sum (Qty) as Sum_qty from dbo. Inventory GROUP by Item,color with rollup
Focus: Distinguish between null and summary values use Grouping (Item) or Grouping (Color) function to differentiate when the Grouping function returns 1 when the proof is aggregated, otherwise the true null value
1. Cube------======---
Select Case-Grouping (item) = 1 Then ' rollup ' ELSE ISNULL (item, ' unknown ') End as item,case when Grouping (Color) = 1 Then ' summary ' E LSE ISNULL (Color, ' unknown ') End as Item,sum (Qty) as Sum_qty from dbo. Inventory GROUP by Item,color with Cube
2. For ROLLUP------======select case when Grouping (item) = 1 Then ' rollup ' ELSE ISNULL (item, ' unknown ') End as item,case when GroupI Ng (color) = 1 Then ' rollup ' ELSE ISNULL (color, ' unknown ') End as Item,sum (Qty) as Sum_qty from dbo. Inventory GROUP by Item,color with rollup
3. How to return a summary of a specified dimension: 1. Using a derived table and then making a where condition filter on the derived table is a subquery dataset that summarizes the SELECT query statement and then filters the Item is not null and the Color is null and so forth from the dataset. 2. Use grouping sets to specify that only the required groupings need to be obtained
Without null, NULL for this case:
With NULL, NULL for this case
Original link This article by Bean John Blog Backup expert remote One click release
8 Eighth chapter CTE recursion and Grouping summary Advanced Section (cube) (reproduced)
