高级分组rollup,cube操作(二)
14300 0 0
20 6600 0 0
20 20900 0 1
30 12100 0 0
30 15290 0 0
30 27390 0 1
40 7150 0 0
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
40 7150 0 1
50 40040 0 0
50 70730 0 0
50 61270 0 0
50 172040 0 1
60 31680 0 0
60 31680 0 1
70 11000 0 0
70 11000 0 1
80 61000 0 0
80 243500 0 0
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
80 304500 0 1
90 34000 0 0
90 24000 0 0
90 58000 0 1
100 12000 0 0
100 39600 0 0
100 51600 0 1
110 12000 0 0
110 8300 0 0
110 20300 0 1
716400 1 1
33 rows selected.
Cube分组SQL> select department_id,sum(salary),grouping(department_id),grouping(job_id) from emp group by cube(department_id,job_id);
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
7000 0 1
716400 1 1
34000 1 0
12000 1 0
12000 1 0
7150 1 0
14300 1 0
6600 1 0
11000 1 0
12100 1 0
61000 1 0
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
7000 0 0
250500 1 0
40040 1 0
4840 1 0
24000 1 0
31680 1 0
15290 1 0
70730 1 0
61270 1 0
8300 1 0
39600 1 0
DEPARTMENT_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID)
------------- ----------- ----------------------- ----------------
10 4840 0 1
10 4840 0 0
20 20900 0 1
20 14300 0 0
20 6600