測試用戶: 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>