题解 | #查找在职员工自入职以来的薪水涨幅情况#

难点

这道题的关键（难点）在于：

• 对象是在职员工
• growth运算逻辑是：现在的salary - 最初的salary

思路：

• 在职员工：to_date = '9999-01-01'
• 现在的salary： to_date = '9999-01-01'
• 最初的salary： WHERE salaries.from_date = employees.hire_date

• 现在的薪资是to_date = '9999-01-01'的 薪资
• 最初的薪资正是入职日期的薪资（关键！）

下面是我的代码，我认为逻辑比较简明清晰，可以作为参考。

WITH current_salaries AS (
    SELECT e.emp_no, s.salary AS current_salary
    FROM employees e
    JOIN salaries s
    ON e.emp_no = s.emp_no
    WHERE to_date = '9999-01-01'
),
initial_salaries AS (
    SELECT e.emp_no, s.salary AS initial_salary
    FROM employees e
    JOIN salaries s
    ON e.emp_no = s.emp_no
    WHERE e.hire_date = s.from_date
)

SELECT c.emp_no, (c.current_salary - i.initial_salary) AS growth
FROM current_salaries c
JOIN initial_salaries i
ON c.emp_no = i.emp_no
ORDER BY growth;