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

select
    dept_no,
    emp_no,
    maxSalary
from
    (
        select
            de.dept_no,
            de.emp_no,
            sal.salary,
            max(salary) OVER (
                PARTITION BY
                    DEPT_NO
            ) as maxSalary
        from
            dept_emp as de
            inner join salaries as sal on de.emp_no = sal.emp_no
    ) as t1
where
    salary = maxSalary

先用窗口函数over得到每个部门的最大薪资，然后再用每个员工的薪资去和最大薪资做比较，得到员工编号