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

使用SQL語(yǔ)句生成帶有小計(jì)合計(jì)的數(shù)據(jù)集:
    測(cè)試用戶(hù): scott
    測(cè)試用表: dept,emp
    //////////////////////////////////
    //檢索出需要進(jìn)行統(tǒng)計(jì)的數(shù)據(jù)集
    select dept.dname,emp.job,sal from emp,dept
    where emp.deptno=dept.deptno;
    //////////////////////////////////
    //根據(jù)部門(mén)名稱(chēng)以及職位進(jìn)行匯總,并為每個(gè)部門(mén)
    生成’小計(jì)’,最后生成’合計(jì)’.
    select
    decode(grouping(dept.dname),1,’合計(jì):’,dept.dname)dname,
    decode(grouping(emp.job)+grouping(dept.dname),1,’小計(jì):’,emp.job)job,sum(sal) sum_sal from emp,dept where emp.deptno=dept.deptno group by rollup(dept.dname,emp.job);
    運(yùn)行結(jié)果如下:
    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,’合計(jì):’,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 小計(jì): 8750
    RESEARCH ANALYST 6000
    RESEARCH CLERK 1900
    RESEARCH MANAGER 2975
    RESEARCH 小計(jì): 10875
    SALES CLERK 950
    SALES MANAGER 2850
    SALES SALESMAN 5600
    DNAME JOB SUM_SAL
    -------------- --------- ----------
    SALES 小計(jì): 9400
    合計(jì): 29025
    已選擇13行。