题解 | #获取每个部门中当前员工薪水最高的相关信息#窗口函数得到各部门的最高薪水

select distinct ms.dept_no,a.emp_no,ms.ma
from (
    select distinct de.dept_no, max(s.salary) over(partition by de.dept_no) as ma
    from dept_emp de
    join salaries s on de.emp_no = s.emp_no)ms
join (     
    select de.dept_no, de.emp_no, s.salary
    from salaries s
    join dept_emp de on de.emp_no = s.emp_no) a
    on ms.dept_no = a.dept_no and ms.ma = a.salary
order by ms.dept_no;