2021/2/14 [SQL]查找所有员工自入职以来的薪水涨幅情况

解题思路

用 employees 表中的 emp_no 和 hire_date 去查找 salaries 表中该员工入职时的工资，然后再使用 emp_no 去查找 to_date = '9999-01-01' 的工资，两个相减，就是入职以来工资的涨幅。

代码实现

SELECT t1.emp_no, (t2.salary - t1.salary) growth
FROM (
    SELECT e.emp_no, s.salary
    FROM employees e
    LEFT JOIN salaries s
    ON e.emp_no = s.emp_no
    WHERE e.hire_date = s.from_date
) t1
RIGHT JOIN (
    SELECT e.emp_no, s.salary
    FROM employees e
    LEFT JOIN salaries s
    ON e.emp_no = s.emp_no
    WHERE s.to_date = '9999-01-01'
) t2
ON t1.emp_no = t2.emp_no
ORDER BY growth;

或

SELECT t1.emp_no, (t2.salary - t1.salary) growth
FROM employees e
LEFT JOIN salaries t1
ON e.emp_no = t1.emp_no
LEFT JOIN salaries t2
ON e.emp_no = t2.emp_no
WHERE e.hire_date = t1.from_date
AND t2.to_date = '9999-01-01'
ORDER BY growth;