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

select 
dept_no,
emp_no,
salary as maxSalary
from 
(
select 
d.dept_no,
d.emp_no,
salary,
row_number() over (partition by d.dept_no order by salary desc) as r
from dept_emp d
left join salaries s
on d.emp_no = s.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01'
)t
where r = 1
order by dept_no