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

select
    dept_no,
    emp_no,
    salary as maxSalary
from
    (
        select
            t1.dept_no as dept_no,
            t2.emp_no as emp_no,
            t2.salary as salary,
            rank() over (
                partition by
                    t1.dept_no
                order by
                    t2.salary desc
            ) as ranking
        from
            dept_emp as t1
            inner join salaries as t2 on t1.emp_no = t2.emp_no
        group by
            dept_no,
            emp_no,
            salary
    ) as tp
where ranking = 1;