用SQL語句生成帶有小計合計的數(shù)據(jù)集腳本

使用SQL語句生成帶有小計合計的數(shù)據(jù)集:
    測試用戶: scott
    測試用表: dept,emp
    //////////////////////////////////
    //檢索出需要進行統(tǒng)計的數(shù)據(jù)集
    select dept.dname,emp.job,sal from emp,dept
    where emp.deptno=dept.deptno;
    //////////////////////////////////
    //根據(jù)部門名稱以及職位進行匯總,并為每個部門
    生成’小計’,最后生成’合計’.
    select
    decode(grouping(dept.dname),1,’合計:’,dept.dname)dname,
    decode(grouping(emp.job)+grouping(dept.dname),1,’小計:’,emp.job)job,sum(sal) sum_sal from emp,dept where emp.deptno=dept.deptno group by rollup(dept.dname,emp.job);
    運行結果如下:
    SQL> select dept.dname,emp.job,sal from emp,d
    DNAME JOB SAL
    -------------- --------- ----------
    RESEARCH CLERK 800
    SALES SALESMAN 1600
    SALES SALESMAN 1250
    RESEARCH MANAGER 2975
    SALES SALESMAN 1250
    SALES MANAGER 2850
    ACCOUNTING MANAGER 2450
    RESEARCH ANALYST 3000
    ACCOUNTING PRESIDENT 5000
    SALES SALESMAN 1500
    RESEARCH CLERK 1100
    DNAME JOB SAL
    -------------- --------- ----------
    SALES CLERK 950
    RESEARCH ANALYST 3000
    ACCOUNTING CLERK 1300
    已選擇14行。
    SQL> select
    2 decode(grouping(dept.dname),1,’合計:’,de
    3 decode(grouping(emp.job)+grouping(dept.d
    ept where emp.deptno=dept.deptno group by rol
    DNAME JOB SUM_SAL
    -------------- --------- ----------
    ACCOUNTING CLERK 1300
    ACCOUNTING MANAGER 2450
    ACCOUNTING PRESIDENT 5000
    ACCOUNTING 小計: 8750
    RESEARCH ANALYST 6000
    RESEARCH CLERK 1900
    RESEARCH MANAGER 2975
    RESEARCH 小計: 10875
    SALES CLERK 950
    SALES MANAGER 2850
    SALES SALESMAN 5600
    DNAME JOB SUM_SAL
    -------------- --------- ----------
    SALES 小計: 9400
    合計: 29025
    已選擇13行。