Oracle PL/SQL之GROUP BY CUBE
Oracle PL/SQL之GROUP BY CUBE
ROLLUP会根据GROUP BY后面的字段从右到左逐步以去掉右边一个字段,逐步向上累计求和,
CUBE则会对GROUP BY后面的字段所有的组合进行分组求和。
CUBE eg1:
- SELECT department_id, job_id, SUM (salary)
- FROM employees
- WHERE department_id < 60
- GROUP BY CUBE (job_id, department_id);
- DEPARTMENT_ID JOB_ID SUM(SALARY)
- ------------- ---------- -----------
- 211200
- 10 4400
- 20 19000
- 30 24900
- 40 6500
- 50 156400
- HR_REP 6500
- 40 HR_REP 6500
- MK_MAN 13000
- 20 MK_MAN 13000
- MK_REP 6000
- 20 MK_REP 6000
- PU_MAN 11000
- 30 PU_MAN 11000
- ST_MAN 36400
- 50 ST_MAN 36400
- AD_ASST 4400
- 10 AD_ASST 4400
- PU_CLERK 13900
- 30 PU_CLERK 13900
- SH_CLERK 64300
- DEPARTMENT_ID JOB_ID SUM(SALARY)
- ------------- ---------- -----------
- 50 SH_CLERK 64300
- ST_CLERK 55700
- 50 ST_CLERK 55700
- 24 rows selected.
CUBE eg2:
- SELECT department_id, job_id, SUM (salary)
- FROM employees
- WHERE department_id < 60
- GROUP BY CUBE (department_id, job_id);
- DEPARTMENT_ID JOB_ID SUM(SALARY)
- ------------- ---------- -----------
- 211200
- HR_REP 6500
- MK_MAN 13000
- MK_REP 6000
- PU_MAN 11000
- ST_MAN 36400
- AD_ASST 4400
- PU_CLERK 13900
- SH_CLERK 64300
- ST_CLERK 55700
- 10 4400
- 10 AD_ASST 4400
- 20 19000
- 20 MK_MAN 13000
- 20 MK_REP 6000
- 30 24900
- 30 PU_MAN 11000
- 30 PU_CLERK 13900
- 40 6500
- 40 HR_REP 6500
- 50 156400
- DEPARTMENT_ID JOB_ID SUM(SALARY)
- ------------- ---------- -----------
- 50 ST_MAN 36400
- 50 SH_CLERK 64300
- 50 ST_CLERK 55700
- 24 rows selected.
评论暂时关闭