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

select t1.dept_no, t2.emp_no, t1.salary from (select a.dept_no, max(b.salary) salary from dept_emp a join salaries b on a.emp_no = b.emp_no and a.to_date = '9999-01-01' and b.to_date = '9999-01-01' group by a.dept_no)as t1 join (select a.dept_no, a.emp_no, b.salary from dept_emp a join salaries b on a.emp_no = b.emp_no and a.to_date = '9999-01-01' and b.to_date = '9999-01-01') as t2 on t1.dept_no = t2.dept_no and t1.salary = t2.salary order by t1.dept_no