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

select b.dept_no,b.emp_no,b.salary
from
(select de.dept_no,de.emp_no,s.salary from dept_emp de join salaries s ON de.emp_no=s.emp_no
where de.to_date='9999-01-01' and s.to_date='9999-01-01') b
where (b.dept_no,b.salary) IN (select c.dept_no,max(c.salary) as salary
from (select de.dept_no,de.emp_no,s.salary
from dept_emp de join salaries s ON de.emp_no=s.emp_no
where de.to_date='9999-01-01' and s.to_date='9999-01-01') c
group by c.dept_no)
order by b.dept_no