充分利用子查询
先查出入职时的工资表
select a.emp_no,b.salary from employees a left join salaries b
on a.emp_no = b.emp_no on a.birth_date = b.from_date
再查出现在的工资表
select a.emp_no,b.salary from employees a left join salaries b
on a.emp_no = b.emp_no where b.to_date = '9999-01-01'
最后将两个表联结即可
select d.emp_no, (d.salary-c.salary) as growth
from
(select a.emp_no,b.salary from employees a left join salaries b
on a.emp_no = b.emp_no and a.hire_date = b.from_date ) c
INNER JOIN
(select a.emp_no,b.salary from employees a left join salaries b
on a.emp_no = b.emp_no where b.to_date = '9999-01-01') d
on c.emp_no = d.emp_no
order by growth;