group by cube 用法

/*GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)， 最后对全表进行GROUP BY操作。 */--构造环境drop table dept purge;drop table emp purge;create table dept as select * from scott.dept;create table emp  as select * from scott.emp;--------------------------------------------------------------------------------------------------------------------------------------------------------------------set term offset heading onset verify offset feedback offset linesize 2000set pagesize 30000set long 999999999set longchunksize 999999set autotrace off-- CUBE分组SELECT  a.dname,b.job, SUM(b.sal) sum_salFROM dept a,emp b WHERE a.deptno = b.deptnoGROUP  BY CUBE(a.dname,b.job);DNAME          JOB          SUM_SAL-------------- --------- ----------                              29025               CLERK           4150               ANALYST         6000               MANAGER         8275               SALESMAN        5600               PRESIDENT       5000SALES                          9400SALES          CLERK            950SALES          MANAGER         2850SALES          SALESMAN        5600RESEARCH                      10875RESEARCH       CLERK           1900RESEARCH       ANALYST         6000RESEARCH       MANAGER         2975ACCOUNTING                     8750ACCOUNTING     CLERK           1300ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       5000----部分CUBE分组SELECT  a.dname,b.job, SUM(b.sal) sum_salFROM dept a,emp b WHERE a.deptno = b.deptnoGROUP  BY a.dname,CUBE(b.job);DNAME          JOB          SUM_SAL-------------- --------- ----------SALES                          9400SALES          CLERK            950SALES          MANAGER         2850SALES          SALESMAN        5600RESEARCH                      10875RESEARCH       CLERK           1900RESEARCH       ANALYST         6000RESEARCH       MANAGER         2975ACCOUNTING                     8750ACCOUNTING     CLERK           1300ACCOUNTING     MANAGER         2450ACCOUNTING     PRESIDENT       5000