先分别查找入职工资与当前工资,然后二者作差。
代码:
SELECT cur.emp_no, (cur.salary - pre.salary) AS growth
FROM
(
SELECT e1.emp_no, s1.salary
FROM employees AS e1
INNER JOIN salaries AS s1
ON e1.emp_no = s1.emp_no
WHERE e1.hire_date = s1.from_date
) AS pre
INNER JOIN
(
SELECT s2.emp_no, s2.salary
FROM salaries AS s2
WHERE s2.to_date = '9999-01-01'
) AS cur
ON pre.emp_no = cur.emp_no
order by growth