用SQL語句生成帶有小計合計的數據集腳本

使用SQL語句生成帶有小計合計的數據集: 測試用戶: scott 測試用表: dept,emp ////////////////////////////////// //檢索出需要進行統計的數據集 select dept.dname,emp.job,sal from emp,dept where emp.deptno=dept.deptno; ////////////////////////////////// //根據部門名稱以及職位進行彙總,並爲每個部門 生成'小計',最後生成'合計'. 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行。 SQL>