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

SELECT dm.dept_no, dm.emp_no, s.salary
FROM
    dept_emp dm
    JOIN salaries s ON dm.emp_no = s.emp_no
    JOIN (
        SELECT
            de.dept_no,
            max(salary) as salary
        FROM
            dept_emp de
            JOIN salaries ss ON de.emp_no = ss.emp_no
        GROUP BY
            de.dept_no
    ) ds ON dm.dept_no = ds.dept_no
WHERE
    s.salary = ds.salary
ORDER BY dm.dept_no;