| 導購 | 订阅 | 在线投稿
分享
 
 
 

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

來源:互聯網  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>
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有