题解 | #获取每个部门中当前员工薪水最高的相关信息#

select distinct
    res.deptno,
    m.emp_no,
    res.maxSalary
from
    dept_emp d1 join
    salaries m
    join (
        select
            e.dept_no as deptno,
            max(s.salary) as maxSalary
        from
            dept_emp e
            join salaries s on e.emp_no = s.emp_no
        group by
            dept_no
        order by
            dept_no
    ) as res
where
    res.maxSalary = m.salary and m.emp_no = d1.emp_no and d1.dept_no = res.deptno
order by  res.deptno;

首先查询出部门和对应的最高工资

select
	e.dept_no as deptno,
	max(s.salary) as maxSalary
from
	dept_emp e
join 
	salaries s on e.emp_no = s.emp_no
group by
	dept_no
order by
	dept_no;

接着我们将这个表作为临时表与salaries表连接,查询出薪资为分组最高薪资的员工信息

select distinct
    res.deptno,
    m.emp_no,
    res.maxSalary
from
    salaries m
    join (
        select
            e.dept_no as deptno,
            max(s.salary) as maxSalary
        from
            dept_emp e
            join salaries s on e.emp_no = s.emp_no
        group by
            dept_no
        order by
            dept_no
    ) as res
where
    res.maxSalary = m.salary
order by  res.deptno;

存在一个问题,我们查询到的只是薪资等于分组最高薪资的员工,但无法确定这个员工是否是这个组的,为了确定,我们还需要将dept表连接起来,dept表需要确定与salaries表查询的是一个员工,还需要确定dept表与临时表的部门编号一样,即最开始的代码