The fourth day of SQL learning--sql about with Cube,with Rollup and grouping interpretation and presentation _mssql

About the WITH cube, with rollup and grouping

The specific differences between cube and ROLLUP are found by looking at the Help documentation for SQL 2005 :

Cube produces 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.

And look at the explanation for grouping:

When a row is added by the cube or ROLLUP operator, the function causes the output value of the additional column to be 1, and when the row is not added by the cube or the ROLLUP operator, the function causes the output value of the additional column to be 0.

Grouping is allowed only in the select list associated with the GROUP BY clause that contains the CUBE or ROLLUP operator.

When you see the above explanation must be very vague, unintelligible and do not know how to use, the following through the example operation to experience:

First build the table (dbo. Peopleinfo):

Copy Code code as follows:

CREATE TABLE [dbo]. [Peopleinfo] (
[ID] [int] IDENTITY (1,1) not NULL,
[Name] [NCHAR] (a) COLLATE chinese_prc_ci_as NULL,
[Numb] [NCHAR] (a) COLLATE chinese_prc_ci_as not NULL,
[Phone] [NCHAR] (a) COLLATE chinese_prc_ci_as NULL,
[Fenshu] [INT] Null
) on [PRIMARY]

To insert data into a table:

Copy Code code as follows:

INSERT into Peopleinfo ([Name],numb,phone,fenshu) VALUES (' Li Huan ', ' 3223 ', ' 1365255 ', 80)
INSERT into Peopleinfo ([Name],numb,phone,fenshu) VALUES (' Li Huan ', ' 322123 ', ' 1 ', 90)
INSERT into Peopleinfo ([Name],numb,phone,fenshu) VALUES (' Li name ', ' 3213112352 ', ' 13152 ', 56)
INSERT into Peopleinfo ([Name],numb,phone,fenshu) VALUES (' Li name ', ' 32132312 ', ' 13342563 ', 60)
INSERT into Peopleinfo ([Name],numb,phone,fenshu) VALUES (' Wang Hua ', ' 3223 ', ' 1365255 ', 80)

Query for all inserted data:

Copy Code code as follows:

SELECT * FROM dbo. Peopleinfo

The result is as shown in figure:

Operation One: First try: 1, query all data, 2, use Group by query all data, 3, with Cube. The comparison of these three types of cases

The SQL statement is as follows:

Copy Code code as follows:

SELECT * FROM dbo. Peopleinfo--1, query all data;
SELECT [Name],numb,sum (Fenshu) from dbo. Peopleinfo GROUP BY [Name],numb--2, using GROUP by to query all data;
SELECT [Name],numb,sum (Fenshu) from dbo. Peopleinfo GROUP BY [Name],numb with Cube--3, using the WITH cube. The comparison of these three types of cases

The result is as shown in figure:

Results Analysis:

In the third (with the WITH cube) Why do you get more out of null field values? You can see what's going on with the combination of the analysis. Take the third data (Li Huan, null,170) as an example: it is only the name is "Li Huan" divided into a group, but did not consider "numb", so there are more than the third data, also explains how 170 came. So it is with the others. Again, the explanation of the Help document: Cube produces a result set that shows the aggregation of all combinations of values in the selected column, and it is found that many.

Operation two:1, use with cube;2, use with rollup these two kinds of situation comparison

The SQL statement is as follows:

Copy Code code as follows:

SELECT [Name],numb,sum (Fenshu) from dbo. Peopleinfo GROUP BY [Name],numb with Cube--using the WITH cube.
SELECT [Name],numb,sum (Fenshu) from dbo. Peopleinfo GROUP BY [Name],numb and Rollup-with the WITH rollup.

The result is as shown in figure:

Results Analysis:

Why is the WITH cube more than the WITH rollup part? It turned out to be not displayed, grouped by "numb" regardless of the "name" data. Review the explanation of the Help document: ROLLUP The resulting set of results shows the aggregation of a hierarchy of values in the selected column, and what is the standard for a "certain level"? My guess is that the fields closest to group up must be considered within a group.

Proof Conjecture Example:

Action: Compare the SQL statement with the two group up swap field position with an SQL statement that adds a field after group up:

The SQL statement is as follows:

Copy Code code as follows:

SELECT [Name],numb from dbo. Peopleinfo GROUP BY [Name],numb with Rollup
SELECT [Name],numb from dbo. Peopleinfo GROUP BY Numb,[name] with rollup
SELECT [Name],numb,phone from dbo. Peopleinfo GROUP BY [Name],numb,phone with Rollup

The result is as shown in figure:

The comparison of the results shows that the conjecture is correct.

---------------------------------------------------grouping------------------------------------------- ------

Now let's look at the grouping example:

The SQL statement looks at the combination with the WITH rollup (as with the WITH Cube):

Copy Code code as follows:

SELECT [Name],numb,grouping (Numb) from dbo. Peopleinfo GROUP BY [Name],numb with Rollup

The result is as shown in figure:

Results Analysis:

Explanation of the Help document: When a row is added by the cube or ROLLUP operator, the function causes the output value of the additional column to be 1, and when the row is not added by the cube or the ROLLUP operator, the function causes the output value of the additional column to be 0. It's easy to understand and that's not much to explain.