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

select
    m.dept_no,
    m.emp_no,
    m.salary as maxSalary
from
    (
        select
            RANK() OVER (
                PARTITION BY
                    t.dept_no
                ORDER BY
                    t.salary DESC
            ) as num,
            t.dept_no,
            t.emp_no,
            t.salary
        from
            (
                select
                    d.dept_no,
                    d.emp_no,
                    s.salary
                from
                    dept_emp d
                    inner join salaries s on s.emp_no = d.emp_no
            ) t
    ) m
where
    m.num = 1