题解 | 三个JOIN,四个条件

select
    D.dept_no,
    D.emp_no,
    S.salary maxSalary
FROM
    dept_emp D
    LEFT JOIN (
        SELECT
            D.dept_no,
            MAX(S.salary) maxSalary
        FROM
            dept_emp D
            JOIN salaries S ON D.emp_no = S.emp_no
        GROUP BY
            D.dept_no
    ) T ON D.dept_no = T.dept_no
    JOIN salaries S ON D.emp_no = S.emp_no
    AND S.salary = T.maxSalary
ORDER BY
    D.dept_no ASC