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

setp1:双表根据员工编号联结按部门编号分组取得每个部门最高工资 结果集取名r2
setp2:双表再按员工编号联结 得到结果集 r1
setp3:where 过滤（r2.dept_no=r1.dept_no and r2.maxSalary=r1.salary）得到 结果
setp4:按部门编号排序，取别名返回
select r1.dept_no,r1.emp_no,r1.salary as maxSalary
from (select d.*,s.salary
from dept_emp d
left join salaries s
on d.emp_no=s.emp_no) r1
where exists(select *
from ( select d.dept_no,max(s.salary) maxSalary
from dept_emp d
left join salaries s
on d.emp_no=s.emp_no
group by d.dept_no) r2
where r2.dept_no=r1.dept_no
and r2.maxSalary=r1.salary)
order by r1.dept_no