使用sql语句直接生成带有’小计’,’合计’的数据集

在软件开发过程中经常要做一些报表，而且大部分报表都需要用到根据某一列进行统计，计算出’小计’以及’合计’项目。一般来说大家都是在前台程序中进行处理，其实，用sql语句就可以很轻松的完成这个功能。
SQL> select decode(grouping(dept.dname),1,'合计:',dept.dname) dname, 2 decode(grouping(emp.job)+grouping(dept.dname),1,'小计:',emp.job) job,sum(sal) sum_sal from emp,dept 3 where emp.deptno=dept.deptno group by rollup(dept.dname,emp.job);
DNAME               JOB                    SUM_SAL
-------------- ---------               ----------
ACCOUNTING CLERK                     1300
ACCOUNTING MANAGER                 2450
ACCOUNTING PRESIDENT               5000
ACCOUNTING 小计:                        8750
RESEARCH ANALYST                     3000
RESEARCH CLERK                         800
RESEARCH MANAGER                    2975
RESEARCH 小计:                            6775
SALES CLERK                               950
SALES MANAGER                          2850
SALES SALESMAN                         5600
SALES 小计:                                  9400 
合计:                                            24925