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

with total_table as (
    select 
        s.emp_no,
        d_e.dept_no,
        s.salary,
        dense_rank() over(partition by d_e.dept_no order by s.salary desc) as rk
    from 
        salaries as s
    left join 
        dept_emp as d_e
    on 
        s.emp_no = d_e.emp_no
)

select
    dept_no,
    emp_no,
    salary
from 
    total_table
where
    rk = 1
order by 
    dept_no