用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>