本文共 2406 字,大约阅读时间需要 8 分钟。
ROLLUP会根据GROUP BY后面的字段从右到左逐步以去掉右边一个字段,逐步向上累计求和,
CUBE则会对GROUP BY后面的字段所有的组合进行分组求和。
CUBE eg1:
SELECT department_id, job_id, SUM (salary) FROM employees WHERE department_id < 60GROUP 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 64300DEPARTMENT_ID JOB_ID SUM(SALARY)------------- ---------- ----------- 50 SH_CLERK 64300 ST_CLERK 55700 50 ST_CLERK 5570024 rows selected.
CUBE eg2:
SELECT department_id, job_id, SUM (salary) FROM employees WHERE department_id < 60GROUP 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 156400DEPARTMENT_ID JOB_ID SUM(SALARY)------------- ---------- ----------- 50 ST_MAN 36400 50 SH_CLERK 64300 50 ST_CLERK 5570024 rows selected.
转载地址:http://tytai.baihongyu.com/