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

select
    d.dept_no,
    s.emp_no,
    salary as maxsalary
from
    dept_emp d,
    salaries s
where
    d.emp_no = s.emp_no
    and d.to_date = '9999-01-01'
    and s.to_date = '9999-01-01'
    and salary = (
        select
            max(salary)
        from
            dept_emp dd,
            salaries ss
        where
            dd.emp_no = ss.emp_no
            and dd.to_date = '9999-01-01'
            and ss.to_date = '9999-01-01'
            and dd.dept_no = d.dept_no
        group by
            dd.dept_no
    )
order by
    d.dept_no