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

薪水涨幅：当前薪资-入职薪资
在职员工：to_date='9999-01-01'

求入职薪资：

SELECT e.emp_no,s.salary 
FROM employees e LEFT JOIN salaries s 
ON e.emp_no=s.emp_no 
AND e.hire_date=s.from_date

求当前薪资：

SELECT emp_no,salary
FROM salaries
WHERE to_date='9999-01-01'

求薪水涨幅，入职薪资表与当前薪资表连接求差
注意：
1、内连接，因当前薪资限定了在职员工，入职薪资则不用单独在做限定
2、order by growth ASC，因为给出结果是按升序排序，如不排序会报错

SELECT e.emp_no,s2.salary-s.salary AS growth
FROM employees e LEFT JOIN salaries s 
ON e.emp_no=s.emp_no 
AND e.hire_date=s.from_date
INNER JOIN salaries s2
ON e.emp_no=s2.emp_no
WHERE s2.to_date='9999-01-01'
ORDER BY 2;