| 订阅 | 在线投稿
分享
 
 
 

用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- 王朝网络 版权所有