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

select d.dept_no,d.emp_no,s.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'
where s.salary = (select max(s2.salary)
             from  dept_emp de2 inner join salaries s2
             on de2.emp_no = s2.emp_no
             and de2.to_date = '9999-01-01'
             and s2.to_date = '9999-01-01'
             where de2.dept_no = d.dept_no
             group by de2.dept_no
)

order by d.dept_no