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

WITH t1 AS (
    SELECT *
    FROM dept_emp
    WHERE to_date = '9999-01-01'
),
t2 AS (
    SELECT *
    FROM salaries
    WHERE to_date = '9999-01-01'
),
t3 AS (
    SELECT t1.dept_no,
           t2.emp_no,
           t2.salary,
           RANK()
               over (PARTITION BY dept_no
                   ORDER BY salary DESC) AS ranking
    FROM t2
    LEFT JOIN t1
    ON t2.emp_no = t1.emp_no
)
SELECT dept_no,
       emp_no,
       salary
FROM t3
WHERE ranking = 1
ORDER BY dept_no ASC
;

# 这道题通过率低是因为题目要求别名但实际没有！