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

select uni.dept_no, uni.emp_no, max_salary.salary
from
    (select d.dept_no, s.emp_no, s.salary
     from dept_emp d inner join salaries s 
     on d.emp_no = s.emp_no 
    ) as uni, /* 先查部门编号,员工编号,当前薪水；三者关联*/
    (select d.dept_no, max(s.salary) as salary
     from dept_emp d inner join salaries s 
     on d.emp_no = s.emp_no
#      and d.to_date = '9999-01-01'
#      and s.to_date = '9999-01-01'   
     group by d.dept_no
    ) as max_salary /* 再查部门编号,当前最高薪水；两者关联*/
where uni.salary = max_salary.salary    -- 薪水和最高薪水关联
and uni.dept_no = max_salary.dept_no    -- 部门编号关联，得出每个部门最高薪水
order by uni.dept_no;             -- 根据部门编号序排列