Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。
如下SQL
select t.*, t.rowid from group_test t;
MANAGER 20 2975 AAAQMEAAEAAEGk/AAA
SALESMAN 30 1250 AAAQMEAAEAAEGk/AAB
MANAGER 30 2850 AAAQMEAAEAAEGk/AAC
MANAGER 10 2450 AAAQMEAAEAAEGk/AAD
ANALYST 20 3000 AAAQMEAAEAAEGk/AAE
PRESIDENT 10 5000 AAAQMEAAEAAEGk/AAF
SALESMAN 30 1500 AAAQMEAAEAAEGk/AAG
CLERK 20 1100 AAAQMEAAEAAEGk/AAH
CLERK 30 950 AAAQMEAAEAAEGk/AAI
ANALYST 20 3000 AAAQMEAAEAAEGk/AAJ
CLERK 10 1300 AAAQMEAAEAAEGk/AAK
CLERK 20 800 AAAQMEAAEAAEGlAAAA
SALESMAN 30 1600 AAAQMEAAEAAEGlAAAB
SALESMAN 30 1250 AAAQMEAAEAAEGlAAAC
select job, deptno, sum(sal) total_sal
from group_test
group by rollup(job, deptno);
JOB DEPTNO TOTAL_SAL
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
CLERK 4150
ANALYST 20 6000
ANALYST 6000
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
MANAGER 8275
SALESMAN 30 5600
SALESMAN 5600
PRESIDENT 10 5000
PRESIDENT 5000
29025
select job, deptno, sum(sal) total_sal
from group_test
group by cube(job, deptno);
| JOB | DEPTNO | TOTAL_SAL |
| 29025 | ||
| 10 | 8750 | |
| 20 | 10875 | |
| 30 | 9400 | |
| CLERK | 4150 | |
| CLERK | 10 | 1300 |
| CLERK | 20 | 1900 |
| CLERK | 30 | 950 |
| ANALYST | 6000 | |
| ANALYST | 20 | 6000 |
| MANAGER | 8275 | |
| MANAGER | 10 | 2450 |
| MANAGER | 20 | 2975 |
| MANAGER | 30 | 2850 |
| SALESMAN | 5600 | |
| SALESMAN | 30 | 5600 |
| PRESIDENT | 5000 | |
| PRESIDENT | 10 | 5000 |
select decode(grouping_id(job, deptno), 1, '合计', job || deptno) as group_col,
sum(sal) total_sal
from group_test
group by rollup(job, deptno);
| GROUP_COL | TOTAL_SAL |
| CLERK10 | 1300 |
| CLERK20 | 1900 |
| CLERK30 | 950 |
| 合计 | 4150 |
| ANALYST20 | 6000 |
| 合计 | 6000 |
| MANAGER10 | 2450 |
| MANAGER20 | 2975 |
| MANAGER30 | 2850 |
| 合计 | 8275 |
| SALESMAN30 | 5600 |
| 合计 | 5600 |
| PRESIDENT10 | 5000 |
| 合计 | 5000 |
| 29025 |
