Oracle PL/SQL之GROUP BY CUBE

最新推荐文章于 2025-06-01 13:10:28 发布

最新推荐文章于 2025-06-01 13:10:28 发布 · 104 阅读

文章标签：

#数据库

本文详细解析了SQL中GROUP BY子句的CUBE和ROLLUP操作的使用方法及区别。通过两个具体例子展示了如何利用这些操作对数据进行多维度的分组汇总，特别适用于商业智能和报表制作等场景。

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.