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

-- 组内 topN 问题，窗口函数，部门分组，薪水排序，取 top1

select dept_no
      ,emp_no
      ,salary as maxSalary
from (
select dept_no
      ,dep.emp_no
      ,salary
      ,dense_rank() over(partition by dept_no order by salary desc) as dep_sal_rank
from dept_emp as dep
left join salaries as sal
on dep.emp_no=sal.emp_no
where dep.to_date='9999-01-01'
and sal.to_date='9999-01-01') as a
where dep_sal_rank=1
order by dept_no