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

with tmp1 as (
select t.dept_no,max(t.salary) as maxSalary
from (
select t1.emp_no,t1.dept_no,t2.salary
from dept_emp as t1
left join salaries as t2 on t1.emp_no = t2.emp_no
) as t
group by t.dept_no
order by t.dept_no),
tmp2 as (
select t1.emp_no,t1.dept_no,t2.salary
from dept_emp as t1
inner join salaries as t2 on t1.emp_no = t2.emp_no)

select s1.dept_no,s2.emp_no,s1.maxSalary as salary
from tmp1 as s1
join tmp2 as s2 on s1.dept_no = s2.dept_no and s1.maxSalary = s2.salary
order by dept_no asc;