Oracle PL/SQL之GROUP BY ROLLUP

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.csdn.net/zhaozhongju/archive/2009/05/13/4177358.aspx