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

select
    d.dept_no,
    d.emp_no,
    s.salary as maxSalary
from
    dept_emp d
    join salaries s on d.emp_no = s.emp_no 
where
    s.salary in (  -- 筛选出每组最高的薪水
        select
            max(salary)
        from
            dept_emp d1
            join salaries s on d1.emp_no = s.emp_no 
            and d1.dept_no = d.dept_no  -- 去除重复元素
        group by
            d1.dept_no
    )
order by
    d.dept_no