Oracle PL/SQL之GROUP BY CUBE

ROLLUP會根據GROUP BY後面的欄位從右至左逐步以去掉右邊一個欄位，逐步向上累計求和，

CUBE則會對GROUP BY後面的欄位所有的組合進行分組求和。

 

CUBE eg1：

 SELECT department_id, job_id, SUM (salary)<br /> FROM employees<br /> WHERE department_id < 60<br />GROUP BY CUBE (job_id, department_id);</p><p>DEPARTMENT_ID JOB_ID SUM(SALARY)<br />------------- ---------- -----------<br /> 211200<br /> 10 4400<br /> 20 19000<br /> 30 24900<br /> 40 6500<br /> 50 156400<br /> HR_REP 6500<br /> 40 HR_REP 6500<br /> MK_MAN 13000<br /> 20 MK_MAN 13000<br /> MK_REP 6000<br /> 20 MK_REP 6000<br /> PU_MAN 11000<br /> 30 PU_MAN 11000<br /> ST_MAN 36400<br /> 50 ST_MAN 36400<br /> AD_ASST 4400<br /> 10 AD_ASST 4400<br /> PU_CLERK 13900<br /> 30 PU_CLERK 13900<br /> SH_CLERK 64300</p><p>DEPARTMENT_ID JOB_ID SUM(SALARY)<br />------------- ---------- -----------<br /> 50 SH_CLERK 64300<br /> ST_CLERK 55700<br /> 50 ST_CLERK 55700</p><p>24 rows selected.

 

CUBE eg2：

SELECT department_id, job_id, SUM (salary)<br /> FROM employees<br /> WHERE department_id < 60<br />GROUP BY CUBE (department_id, job_id);</p><p>DEPARTMENT_ID JOB_ID SUM(SALARY)<br />------------- ---------- -----------<br /> 211200<br /> HR_REP 6500<br /> MK_MAN 13000<br /> MK_REP 6000<br /> PU_MAN 11000<br /> ST_MAN 36400<br /> AD_ASST 4400<br /> PU_CLERK 13900<br /> SH_CLERK 64300<br /> ST_CLERK 55700<br /> 10 4400<br /> 10 AD_ASST 4400<br /> 20 19000<br /> 20 MK_MAN 13000<br /> 20 MK_REP 6000<br /> 30 24900<br /> 30 PU_MAN 11000<br /> 30 PU_CLERK 13900<br /> 40 6500<br /> 40 HR_REP 6500<br /> 50 156400</p><p>DEPARTMENT_ID JOB_ID SUM(SALARY)<br />------------- ---------- -----------<br /> 50 ST_MAN 36400<br /> 50 SH_CLERK 64300<br /> 50 ST_CLERK 55700</p><p>24 rows selected.