Group BY, ROLLUP, CUBE relationships and differences in SQL

Transferred from: http://www.cnblogs.com/dyufei/archive/2009/11/12/2573974.html

Self-evident, look at SQL fully understand, do not need to explain too much, good, share:

The result set generated by the ROLLUP operator is similar to the result set generated by the CUBE operator.

Here are the specific differences between CUBE and ROLLUP:

• 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. ROLLUP Advantages:
  • (1) ROLLUP returns a single result set, while COMPUTE by returns multiple result sets, and multiple result assemblies increase the complexity of the application code.
  • (2) ROLLUP can be used in server cursors, while COMPUTE by is not possible.
  • (3) Sometimes, the query optimizer generates more efficient execution plans for ROLLUP than is generated for COMPUTE by.

Compare the results of group BY, CUBE, and rollup below

To create a table:

CREATE TABLE Depart
(Department char (10), employee char (6), payroll int)

INSERT into depart SELECT ' A ', ' ZHANG ', 100
INSERT into depart SELECT ' A ', ' LI ', 200
INSERT into depart SELECT ' A ', ' WANG ', 300
INSERT into depart SELECT ' A ', ' ZHAO ', 400
INSERT into depart SELECT ' A ', ' DUAN ', 500
INSERT into depart SELECT ' B ', ' DUAN ', 600
INSERT into depart SELECT ' B ', ' DUAN ', 700

Department Employee salary

A ZHANG 100
A LI 200
A WANG 300
A ZHAO 400
A DUAN 500
B DUAN 600
B DUAN 700

(1) GROUP by

SELECT department, employee, SUM (payroll) as Total
From depart
Group BY department, employees

Results:

A DUAN 500
B DUAN 1300
A LI 200
A WANG 300
A ZHANG 100
A ZHAO 400

(2) ROLLUP

SELECT department, employee, SUM (payroll) as Total
From depart
Group by department, employees with ROLLUP

The results are as follows:

A DUAN 500
A LI 200
A WANG 300
A ZHANG 100
A ZHAO 400
A NULL 1500
B DUAN 1300
B NULL 1300
NULL NULL 2800

The rollup result set has more than three aggregated information: the total of department A, the total of department B, and the total sum. The total of Duan in department B is included.

Equivalent to the following SQL statement

SELECT department, employee, SUM (payroll) as Total
From depart
Group BY department, employees
Union
SELECT department, ' NULL ', SUM (payroll) as Total
From depart
GROUP BY department
Union
SELECT ' null ', ' null ', SUM (payroll) as Total
From depart

Results:

A DUAN 500
A LI 200
A NULL 1500
A WANG 300
A ZHANG 100
A ZHAO 400
B DUAN 1300
B NULL 1300
NULL NULL 2800

(3) CUBE

SELECT department, employee, SUM (payroll) as Total
From depart
Group by department, employee with CUBE

Results:

A DUAN 500
A LI 200
A WANG 300
A ZHANG 100
A ZHAO 400
A NULL 1500
B DUAN 1300
B NULL 1300
NULL NULL 2800
NULL DUAN 1800
NULL LI 200
NULL WANG 300
NULL ZHANG 100
NULL ZHAO 400

The result set of cube is 5 rows on the basis of the rollup result set, which is equivalent to the result of a GROUP by employee (that is, cube) on the rollup result set on the Union.

SELECT department, employee, SUM (payroll) as Total
From depart
Group by department, employee with CUBE

Equivalent to the following SQL statement:

SELECT department, employee, SUM (payroll) as Total
From depart
Group by department, employees with ROLLUP

Union

SELECT ' NULL ', employee, SUM (payroll) as Total
From depart
GROUP by employees

Results:

NULL NULL 2800
A NULL 1500
A DUAN 500
A LI 200
A WANG 300
A ZHANG 100
A ZHAO 400
B NULL 1300
B DUAN 1300
NULL DUAN 1800
NULL LI 200
NULL WANG 300
NULL ZHANG 100
NULL ZHAO 400

Needless to say, a comparison of the results will be fully understood.

Group BY, ROLLUP, CUBE relationships and differences in SQL