?Oracle的GROUP BY語句除了最基本的語法外，還支持ROLLUP和CUBE語句。如果是ROLLUP(A, B, C)的話，首先會對(A、B、C)進(jìn)行GROUP BY，然后對(A、B)進(jìn)行GROUP BY，然后是(A)進(jìn)行GROUP BY，最后對全表進(jìn)行GROUP BY操作。如果是GROUP BY CUBE(A, B, C)，則首先會對(A、B、C)進(jìn)行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后對全表進(jìn)行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, '合計(jì)', 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
合計(jì)	4150
ANALYST20	6000
合計(jì)	6000
MANAGER10	2450
MANAGER20	2975
MANAGER30	2850
合計(jì)	8275
SALESMAN30	5600
合計(jì)	5600
PRESIDENT10	5000
合計(jì)	5000
?	29025

總結(jié)

以上是生活随笔為你收集整理的ORACLE ROLLUP CUBE的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。