Oracle PL/SQL之GROUP BY ROLLUP

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

 

沒有ROLLUP的情況下，以下查詢按department_id和job_id進行分組求和：

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

Output：

DEPARTMENT_ID,JOB_ID,SUM(SALARY)<br />50,ST_CLERK,55700<br />50,ST_MAN,36400<br />30,PU_CLERK,13900<br />50,SH_CLERK,64300<br />20,MK_MAN,13000<br />30,PU_MAN,11000<br />10,AD_ASST,4400<br />20,MK_REP,6000<br />40,HR_REP,6500<br />

 

有ROLLUP的情況下：

先對department_id和job_id進行分組求和，再根據department_id累計求和，最後計算總和：

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

Output：

DEPARTMENT_ID,JOB_ID,SUM(SALARY)<br />10,AD_ASST,4400<br />10,,4400<br />20,MK_MAN,13000<br />20,MK_REP,6000<br />20,,19000<br />30,PU_MAN,11000<br />30,PU_CLERK,13900<br />30,,24900<br />40,HR_REP,6500<br />40,,6500<br />50,ST_MAN,36400<br />50,SH_CLERK,64300<br />50,ST_CLERK,55700<br />50,,156400<br />,,211200<br />

 

先對department_id和job_id進行分組求和，再根據job_id累計求和，最後計算總和：

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

Output：

DEPARTMENT_ID,JOB_ID,SUM(SALARY)<br />40,HR_REP,6500<br />,HR_REP,6500<br />20,MK_MAN,13000<br />,MK_MAN,13000<br />20,MK_REP,6000<br />,MK_REP,6000<br />30,PU_MAN,11000<br />,PU_MAN,11000<br />50,ST_MAN,36400<br />,ST_MAN,36400<br />10,AD_ASST,4400<br />,AD_ASST,4400<br />30,PU_CLERK,13900<br />,PU_CLERK,13900<br />50,SH_CLERK,64300<br />,SH_CLERK,64300<br />50,ST_CLERK,55700<br />,ST_CLERK,55700<br />,,211200<br />

 

 

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