Use of Oracle rollup and cube)

Source: http://blog.csdn.net/wh62592855/archive/2009/11/16/4817920.aspx

Rollup is an extension of the Group by clause. It can return subtotal records for each group and total records for all groups.

Cube is an extension of the Group by clause. It returns the subtotal record of each column combination and adds a total record at the end.

At the end of the article, the script for creating related tables and records is attached.

1. Pass a column to Rollup

SQL> select division_id, sum (salary)
2 from employees2
3 group by rollup (division_id)
4 order by division_id;

Div sum (salary)
--------------
Bus 1, 1610000
OPE 1320000
Sal 1, 4936000
Sup 1015000
8881000

SQL>

Let's take a look at what happens if you use normal group by without rollup.

SQL> select division_id, sum (salary)
2 from employees2
3 group by division_id
4 order by division_id;

Div sum (salary)
--------------
Bus 1, 1610000
OPE 1320000
Sal 1, 4936000
Sup 1015000

The final statistics are missing.

2. Pass multiple columns to Rollup

SQL> select division_id, job_id, sum (salary)
2 from employees2
3 group by rollup (division_id, job_id)
4 order by division_id, job_id;

Div job sum (salary)
-----------------
Bus Mgr 530000
Bus pre 800000
Bus wor 280000
Bus 1, 1610000
OPE Eng 245000
OPE Mgr 805000
OPE wor 270000
OPE 1320000
Sal Mgr 4446000
Sal wand 490000
Sal 1, 4936000

Div job sum (salary)
-----------------
Sup Mgr 465000
Sup TEC 115000
Sup wor 435000
Sup 1015000
8881000

16 rows selected.

As you can see, in addition to the last sum record, each division_id group also has a sum record.

Now let's switch the sequence of Data columns in rollup to see how the result works.

SQL> select job_id, division_id, sum (salary)
2 from employees2
3 group by rollup (job_id, division_id)
4 order by job_id, division_id;

Job Div sum (salary)
-----------------
Eng ope 245000
Eng 245000
Mgr bus 530000
Mgr ope 805000
Mgr Sal 4446000
Mgr sup 465000
Mgr 1, 6246000
Pre bus 800000
Pre800000
TEC sup 115000
TEC 115000

Job Div sum (salary)
-----------------
Wor bus 280000
Wor ope 270000.
Wor Sal 490000.
Wor sup 435000.
Wor 1475000
8881000

17 rows selected.

The result is similar, but every job_id group has a sum record.

3. Pass a column to the Cube

SQL> select division_id, sum (salary)
2 from employees2
3 group by cube (division_id)
4 order by division_id;

Div sum (salary)
--------------
Bus 1, 1610000
OPE 1320000
Sal 1, 4936000
Sup 1015000
8881000

It seems that it is no different from rollup.

4. Pass multiple columns to the Cube

SQL> select job_id, division_id, sum (salary)
2 from employees2
3 group by cube (job_id, division_id)
4 order by job_id, division_id;

Job Div sum (salary)
-----------------
Eng ope 245000
Eng 245000
Mgr bus 530000
Mgr ope 805000
Mgr Sal 4446000
Mgr sup 465000
Mgr 1, 6246000
Pre bus 800000
Pre800000
TEC sup 115000
TEC 115000

Job Div sum (salary)
-----------------
Wor bus 280000
Wor ope 270000.
Wor Sal 490000.
Wor sup 435000.
Wor 1475000
Bus 1, 1610000
OPE 1320000
Sal 1, 4936000
Sup 1015000
8881000

21 rows selected.

We can see that the salary is summed according to job_id and division_id. cube returns a record in every job_id, indicating the total salary, and displays the total salary of each division_id near the end, the last record shows the total number of all salaries.

What if I change the order of the two columns? If you are interested, try it yourself.

========================================================== ========================================================== =

Create Table divisions (
Division_id char (3) Constraint divisions_pk primary key,
Name varchar2 (15) not null
);

Create Table jobs (
Job_id char (3) Constraint jobs_pk primary key,
Name varchar2 (20) not null
);

Create Table employees2 (
Employee_id integer constraint employees2_pk primary key,
Division_id char (3)
Constraint employees2_fk_divisions
References divisions (division_id ),
Job_id char (3) references jobs (job_id ),
First_name varchar2 (10) Not null,
Last_name varchar2 (10) Not null,
Salary number (6, 0)
);

Insert into divisions (
Division_id, name
) Values (
'Sal', 'sales'
);

Insert into divisions (
Division_id, name
) Values (
'Ope', 'operations'
);

Insert into divisions (
Division_id, name
) Values (
'Up', 'support'
);

Insert into divisions (
Division_id, name
) Values (
'Bus', 'business'
);
Insert into jobs (
Job_id, name
) Values (
'Wor', 'worker'
);

Insert into jobs (
Job_id, name
) Values (
'Mgr ', 'manager'
);

Insert into jobs (
Job_id, name
) Values (
'Eng', 'engineer'
);

Insert into jobs (
Job_id, name
) Values (
'Tec ', 'regionlist'
);

Insert into jobs (
Job_id, name
) Values (
'Pre', 'President'
);
Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
1, 'bus', 'pre', 'James ', 'Smith', 800000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
2, 'sal', 'mgr ', 'ron', 'johnson, 350000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
3, 'sal ', 'wor', 'fred', 'hobbs ', 140000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
4, 'sup', 'mgr ', 'Susan', 'Jones, 200000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
5, 'sal', 'wor', 'rob', 'green', 350000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
6, 'sup', 'wor', 'jar', 'Brown, 200000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
7, 'up', 'mgr ', 'john', 'grey', 265000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
8, 'sup', 'wor', 'Jean ', 'blue, 110000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
9, 'sup', 'wor', 'henry', 'heyson', 125000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
10, 'ope ', 'mgr', 'kevin ', 'black', 225000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
11, 'ope ', 'mgr', 'keith ', 'long', 165000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
12, 'ope ', 'wor', 'frank', 'Howard', 125000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
13, 'ope', 'wor', 'doreen', 'penn', 145000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
14, 'bus', 'mgr ', 'mark', 'Smith, 155000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
15, 'bus', 'mgr ', 'jill', 'Jones, 175000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
16, 'ope ', 'eng', 'megan ', 'Craig', 245000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
17, 'sup', 'tec ', 'Matthew', 'brant', 115000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
18, 'ope ', 'mgr', 'Tony ', 'clerke', 200000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
19, 'bus', 'mgr ', 'tanya', 'fairway, 200000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
20, 'ope ', 'mgr', 'terry ', 'cliff', 215000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
21, 'sal', 'mgr, 'Steve ', 'green', 275000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
22, 'sal', 'mgr ', 'roy', 'red', 375000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
23, 'sal', 'mgr ', 'Sandra', 'Smith, 335000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
24, 'sal', 'mgr ', 'Gail', 'sil', 225000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
25, 'sal', 'mgr ', 'Gerald', 'gold', 245000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
26, 'sal', 'mgr ', 'eileen', 'lane, 235000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
27, 'sal', 'mgr ', 'doreen', 'upton', 235000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
28, 'sal', 'mgr ', 'jack', 'ewing, 235000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
29, 'sal', 'mgr ', 'Paul', 'owners', 245000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
30, 'sal', 'mgr ', 'manual', 'York, 255000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
31, 'sal', 'mgr ', 'trace', 'yellow', 225000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
32, 'sal', 'mgr ', 'sara', 'white', 235000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
33, 'sal', 'mgr ', 'terry', 'Iron, 225000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
34, 'sal', 'mgr ', 'Christine', 'Brown, 247000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
35, 'sal', 'mgr ', 'john', 'Brown, 249000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
36, 'sal', 'mgr ', 'kelvin', 'trenton', 255000
);

Insert into employees2 (
Employee_id, division_id, job_id, first_name, last_name, salary
) Values (
37, 'bus', 'wor', 'dbin', 'Jones, 280000
);

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/wh62592855/archive/2009/11/16/4817920.aspx