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

with A as
    (
        select 
            d.dept_no,
            max(salary) as maxSalary
        from
            dept_emp d
            join salaries s on d.emp_no = s.emp_no
        where
            s.to_date = '9999-01-01'
        group by
            dept_no
        order by
            dept_no
    ) 

select d.dept_no,d.emp_no,maxSalary from A,salaries s,dept_emp d
where A.dept_no=d.dept_no and
      A.maxSalary=s.salary
and s.emp_no=d.emp_no
order by dept_no