Oracle PL/SQL之GROUP BY ROLLUP
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
评论暂时关闭