查询出部门名称、部门的员工数、部门的平均工资、部门的最低收入雇员姓名和最高收入雇员的姓名

忘是亡心i 2022-08-01 14:57 206阅读 0赞

**如题：**

**查询出部门名称、部门的员工数、部门的平均工资、部门的最低收入雇员姓名和最高收入雇员的姓名**

这是oracle的默认Scott用户下面的emp 和 dept 表间的一道思考题。

###  雇员表(emp) ###

记录了每个雇员的基本信息

NO  字段         类型            描述

1     empno       Number（4）    雇员编号

2     ename       Varchar2(10)      雇员姓名

3     job         Varchar2(9)     工作职位

4     mgr       Number(4)           雇员的领导(经理)编号

5     hierdate      date          入职日期

6     sal          Number(7,2)    月薪/工资

7     comm        Number(7,2)    奖金

8     deptno        Number(2)      雇员所属部门的编号

###  部门表(emp) ###

记录了每个部门的基本信息

NO  字段       类型             描述

1     deptno     Number(2)       部门编号(唯一)

2     dname     Varchar2(14)     部门名称

3     loc       Varchar2(13)     地址

分析：要查询出部门的员工数，平均工资，最低收入雇员姓名和最高收入雇员姓名，必须先知道部门的最高收入和最低收入

第一步：查询出部门名称，部门员工数，部门平均工资，部门最低收入和最高收入

select deptno, count(*), avg(sal), min(sal), max(sal) from emp group by deptno;

![Center][]

第二步：查询最低收入者的姓名

select e.ename 
    from emp e, (select deptno, count(*), avg(sal), min(sal) min_sal, max(sal) max_sal from emp group by deptno) t 
    where e.deptno = e.deptno
    and (e.sal = min_sal);

![Center 1][]

同理，也可以查询最高收入者的姓名

select e.ename 
    from emp e, (select deptno, count(*), avg(sal), min(sal) min_sal, max(sal) max_sal from emp group by deptno) t 
    where e.deptno = e.deptno
    and (e.sal = max_sal);

![Center 2][]  
那如何同时查询最低收入者和最高收入者的姓名呢？

第三步：给第一次查询出来的部门最高收入和最低收入同时关联两张emp 表，分别获取最高收入者和最高收入者的姓名

select d.dname, t.nu, t.avg_sal, t.min_sal, t.max_sal, e.ename, s.ename 
    from dept d, emp e, emp s ,
    (select deptno, count(*) nu, avg(sal) avg_sal, min(sal) min_sal, max(sal) max_sal from emp group by deptno) t
    where d.deptno = t.deptno 
    and (e.deptno = t.deptno and e.sal = t.min_sal) 
    and (s.deptno = t.deptno and s.sal = t.max_sal);

![Center 3][]

[Center]: /images/20220731/e975bd93622141b88ccfd9dd375aabfc.png
[Center 1]: /images/20220731/a1e2a08f235e4305824c6595df3abb5a.png
[Center 2]: /images/20220731/a500b1168bf543db9d283fa689f39364.png
[Center 3]: /images/20220731/b41df257e78843ee8332a57f485ede39.png