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