实验4：oracle 使用组函数和分组统计

1、 显示所有雇员工资的最高、最低、合计和平均值，并对各值按四舍五入处理。

selectround(max(sal),0),round(min(sal),0),round(sum(sal),0),round(avg(sal),0)from emp;

MAX(SAL) MIN(SAL) SUM(SAL) ROUND(AVG(SAL),0) ---------- ---------- ---------- --------------- 5000 800 29025 2073

2、 按工作种类进行分组，显示各个组内雇员工资的最高、最低、合计和平均值，并对各值

按四舍五入处理。

Select

round(max(sal),0),round(min(sal),0),round(sum(sal),0),round(avg(sal),0)from emp group by job;

MAX(SAL) MIN(SAL) SUM(SAL) ROUND(AVG(SAL),0) ---------- ---------- ---------- ----------------- 1300 800 4150 1038 1600 1250 5600 1400 5000 5000 5000 5000 2975 2450 8275 2758 3000 3000 6000 3000

3、 显示各部门名称、位置、部门内的雇员数、部门内的平均工资，平均工资四舍五入到小

数点后2位。 子查询

select emp.deptno, count(empno) m ,round(avg(sal)) a from emp group by emp.deptno; 主查询

select dept.dname,dept.loc,z.m,z.avgsal from dept,(select emp.deptno, count(empno) m ,round(avg(sal)) avgsal from emp group by emp.deptno) z

where dept.deptno=z.deptno;

方法二

select d.dname,d.loc , count(e.ename) ,round(avg(sal),2) from emp e,dept d

where e.deptno=d.deptno group by d.dname,d.loc;

DNAME LOC M AVGSAL -------------- ------------- ---------- ---------- SALES CHICAGO 6 1567 RESEARCH DALLAS 5 2175 ACCOUNTING NEW YORK 3 2917

4 按工作种类进行分组，求出各组内分别在10、20、30部门工作的雇员工资总数，以及各组工资合计，四舍五入到小数点后2位。设置恰当列标题。 员工表

select *from emp order by job; 步骤一

select job,deptno, sum(sal)sum_sal from emp group by job,deptno order by job; 步骤二

select job,sum(sum_sal)

from (select job,deptno, sum(sal)sum_sal from emp group by job,deptno order by job) group by job; 步骤三

select distinct round(e.sumsal),a.job,a.deptno,round(a.sum_sal)

from (select job,sum(sum_sal) sumsal from (select job,deptno, sum(sal)sum_sal from emp group by job,deptno order by job) group by job)e, (select job,deptno, sum(sal)sum_sal from emp group by job,deptno order by job)a where e.job=a.job order by job;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

14 rows selected

JOB DEPTNO SUM_SAL --------- ------ ---------- ANALYST 20 6000 CLERK 10 1300 CLERK 20 1900 CLERK 30 950 MANAGER 10 2450 MANAGER 20 2975 MANAGER 30 2850 PRESIDENT 10 5000 SALESMAN 30 5600

9 rows selected

JOB SUM(SUM_SAL) --------- ------------ CLERK 4150 SALESMAN 5600 PRESIDENT 5000 MANAGER 8275 ANALYST 6000

ROUND(E.SUMSAL) JOB DEPTNO ROUND(A.SUM_SAL) --------------- --------- ------ ---------------- 6000 ANALYST 20 6000 4150 CLERK 10 1300 4150 CLERK 20 1900 4150 CLERK 30 950 8275 MANAGER 10 2450 8275 MANAGER 20 2975 8275 MANAGER 30 2850 5000 PRESIDENT 10 5000 5600 SALESMAN 30 5600

9 rows selected

本文来源：https://www.bwwdw.com/article/o9dx.html