來源:互聯網網民 2008-06-12 07:25:00
評論使用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>
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
使用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>