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

WITH t1 AS (
    SELECT dept_no, d.emp_no, salary
    FROM dept_emp d
    LEFT JOIN salaries s ON d.emp_no = s.emp_no
),
t2 AS (
    SELECT dept_no, emp_no, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_no ORDER BY salary DESC) AS rank_
    FROM t1
)
SELECT dept_no, emp_no, salary
FROM t2
WHERE rank_ = 1;