Oracle PL/SQL之GROUP BY ROLLUP

最新推荐文章于 2022-12-17 08:00:00 发布

t0nsha

最新推荐文章于 2022-12-17 08:00:00 发布

阅读量2.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle - PL/SQL 文章标签： oracle output

本文链接：https://blog.youkuaiyun.com/t0nsha/article/details/6538774

Oracle - PL/SQL 专栏收录该内容

58 篇文章

订阅专栏

本文详细介绍了如何使用SQL中的ROLLUP关键字配合GROUP BY进行数据分组与累积求和，通过具体实例展示了不同维度下数据汇总的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ROLLUP字面意思大概就是向上卷，用在GROUP BY 里面可起到累积求和的作用：

没有ROLLUP的情况下，以下查询按department_id和job_id进行分组求和：

SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY department_id, job_id;

Output：

DEPARTMENT_ID,JOB_ID,SUM(SALARY) 50,ST_CLERK,55700 50,ST_MAN,36400 30,PU_CLERK,13900 50,SH_CLERK,64300 20,MK_MAN,13000 30,PU_MAN,11000 10,AD_ASST,4400 20,MK_REP,6000 40,HR_REP,6500

有ROLLUP的情况下：

先对department_id和job_id进行分组求和，再根据department_id累计求和，最后计算总和：

SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY ROLLUP(department_id, job_id);

Output：

DEPARTMENT_ID,JOB_ID,SUM(SALARY) 10,AD_ASST,4400 10,,4400 20,MK_MAN,13000 20,MK_REP,6000 20,,19000 30,PU_MAN,11000 30,PU_CLERK,13900 30,,24900 40,HR_REP,6500 40,,6500 50,ST_MAN,36400 50,SH_CLERK,64300 50,ST_CLERK,55700 50,,156400 ,,211200

先对department_id和job_id进行分组求和，再根据job_id累计求和，最后计算总和：

SELECT department_id, job_id, SUM (salary) FROM employees WHERE department_id < 60 GROUP BY ROLLUP (job_id, department_id);

Output：

DEPARTMENT_ID,JOB_ID,SUM(SALARY) 40,HR_REP,6500 ,HR_REP,6500 20,MK_MAN,13000 ,MK_MAN,13000 20,MK_REP,6000 ,MK_REP,6000 30,PU_MAN,11000 ,PU_MAN,11000 50,ST_MAN,36400 ,ST_MAN,36400 10,AD_ASST,4400 ,AD_ASST,4400 30,PU_CLERK,13900 ,PU_CLERK,13900 50,SH_CLERK,64300 ,SH_CLERK,64300 50,ST_CLERK,55700 ,ST_CLERK,55700 ,,211200

Ref：http://blog.youkuaiyun.com/zhaozhongju/archive/2009/05/13/4177358.aspx