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

题目要求“自入职以来的薪资涨幅情况”，这句话怎么理解？是自入职以来的最高工资 减去 最低工资？还是目前的工资 减去刚开始入职时的工资？

第一次提交：员工自入职以来的工资最大值减去工资最小值，未通过✖

SELECT emp_no, max(salary) - min(salary) as growth
FROM salaries
WHERE emp_no in (
    SELECT emp_no
    FROM salaries
    WHERE to_date = '9999-01-01'
)
GROUP BY emp_no
ORDER BY growth

第二次提交：员工目前薪资 减去 刚入职时的薪资，通过✔

SELECT
    start.emp_no,
    ( now.salary - start.salary ) as growth
FROM
    (
        SELECT 
            e.emp_no,
            s.salary
        FROM 
            employees e
        JOIN salaries s ON e.hire_date = s.from_date
    ) as start
JOIN (
    SELECT 
        emp_no, 
        salary
    FROM 
        salaries
    WHERE 
        to_date = '9999-01-01'
) as now ON start.emp_no = now.emp_no
ORDER BY growth