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

SELECT T1.dept_no, T1.emp_no, T2.maxSalary
FROM (
    SELECT A.dept_no, A.emp_no, B.salary
    FROM dept_emp AS A
    INNER JOIN salaries AS B ON A.emp_no = B.emp_no
) AS T1
# T1保存每个员工的部门和各自工资情况
INNER JOIN (
    SELECT dept_no, MAX(salary) AS maxSalary
    FROM dept_emp AS A
    INNER JOIN salaries AS B ON A.emp_no = B.emp_no
    GROUP BY dept_no
) AS T2 
# T2保存每个部门的最高工资情况
ON T1.dept_no = T2.dept_no
WHERE T1.salary = T2.maxSalary
# 当员工工资等于员工该部门的最高工资时，该员工即为该部门薪水最高的员工（可能有多个）
ORDER BY T1.dept_no;