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

# 查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，
# 并按照growth进行升序;
# （注: to_date为薪资调整某个结束日期，或者为离职日期，to_date='9999-01-01'时，
# 表示依然在职，无后续调整记录）
# 题目考查了左、右外联接和内联接（任选其一即可），以及求差数（两表数字型字段列）&
# 左右联接ON和WHERE的真假条件过滤的区别
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM
(SELECT e1.emp_no, s1.salary FROM employees AS e1 LEFT OUTER JOIN salaries AS s1 ON e1.emp_no = s1.emp_no
AND e1.hire_date = s1.from_date) AS sStart
INNER JOIN
(SELECT e2.emp_no, s2.salary FROM employees AS e2 LEFT OUTER JOIN salaries AS s2 ON e2.emp_no = s2.emp_no
WHERE s2.to_date = "9999-01-01") AS sCurrent
ON sStart.emp_no = sCurrent.emp_no
ORDER BY growth ASC