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