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

先从问题内部出发，在扩大。


select 
    dept_no,
    emp_no,
    maxSalary
from 
(
    select
    dept_no,
    emp_no,
    maxSalary,
    row_number() over(partition by dept_no order by maxSalary desc) m
    from 
    (
        select 
        dept_no,
        de.emp_no,
        max(salary) maxSalary
        from dept_emp de
        inner join salaries sa
        on de.emp_no = sa.emp_no
        group by dept_no,de.emp_no
    )
    t1
) t2
where m = 1
order by dept_no