Oracle PL/SQL之GROUP BY ROLLUP

来源：互联网发布：淘宝家居花瓶销售方案编辑：程序博客网时间：2024/04/28 01:52

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

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

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

Output：

DEPARTMENT_ID,JOB_ID,SUM(SALARY)50,ST_CLERK,5570050,ST_MAN,3640030,PU_CLERK,1390050,SH_CLERK,6430020,MK_MAN,1300030,PU_MAN,1100010,AD_ASST,440020,MK_REP,600040,HR_REP,6500

有ROLLUP的情况下：

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

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

Output：

DEPARTMENT_ID,JOB_ID,SUM(SALARY)10,AD_ASST,440010,,440020,MK_MAN,1300020,MK_REP,600020,,1900030,PU_MAN,1100030,PU_CLERK,1390030,,2490040,HR_REP,650040,,650050,ST_MAN,3640050,SH_CLERK,6430050,ST_CLERK,5570050,,156400,,211200

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

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

Output：

DEPARTMENT_ID,JOB_ID,SUM(SALARY)40,HR_REP,6500,HR_REP,650020,MK_MAN,13000,MK_MAN,1300020,MK_REP,6000,MK_REP,600030,PU_MAN,11000,PU_MAN,1100050,ST_MAN,36400,ST_MAN,3640010,AD_ASST,4400,AD_ASST,440030,PU_CLERK,13900,PU_CLERK,1390050,SH_CLERK,64300,SH_CLERK,6430050,ST_CLERK,55700,ST_CLERK,55700,,211200

Ref：http://blog.csdn.net/zhaozhongju/archive/2009/05/13/4177358.aspx