Oracle PL/SQL之GROUP BY ROLLUP

k9v427kv35

　　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 employeesWHERE 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