Oracle PL/SQL之GROUP BY ROLLUP

SQL中ROLLUP函数的应用详解

最新推荐文章于 2023-06-28 10:58:02 发布

转载最新推荐文章于 2023-06-28 10:58:02 发布 · 74 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/dtec/blog/46828

本文详细介绍了SQL中ROLLUP字面意思及其在GROUP BY语句中的应用，包括其如何进行分组求和及向上汇总的过程。通过具体示例展示了在不同分组条件下的输出结果，帮助理解其在数据分析和报表生成中的作用。

为什么80%的码农都做不了架构师？>>>

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

原文链接： http://blog.youkuaiyun.com/t0nsha/article/details/6538774

转载于:https://my.oschina.net/dtec/blog/46828