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

SELECT dept_no, emp_no, salary
FROM dept_emp de
JOIN salaries USING(emp_no)
WHERE salary >= ALL(
    SELECT salary
    FROM dept_emp dd
    JOIN salaries USING(emp_no)
    WHERE de.dept_no = dd.dept_no
)
ORDER BY dept_no

思路：通过相关子查询比较此时员工与同部门工资的高低

SELECT dept_no, emp_no, salary
FROM dept_emp de
JOIN salaries USING(emp_no)
WHERE salary >= (
    SELECT MAX(salary)
    FROM dept_emp dd
    JOIN salaries USING(emp_no)
    WHERE de.dept_no = dd.dept_no
)
ORDER BY dept_no