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

# #入职时工资
# select e.emp_no, s.salary as salary_start from employees e
# left join salaries s on e.emp_no=s.emp_no
# and e.hire_date=s.from_date

# #最新工资
# select e.emp_no, s.salary as salary_now from employees e
# left join salaries s on e.emp_no=s.emp_no
# where to_date='9999-01-01'

#两表通过emp_no关联，salary相减
select a.emp_no,b.salary_now-a.salary_start as growth
from 
(select e.emp_no, s.salary as salary_start from employees e
left join salaries s on e.emp_no=s.emp_no
and e.hire_date=s.from_date) a
inner join 
(select e.emp_no, s.salary as salary_now from employees e
left join salaries s on e.emp_no=s.emp_no
where to_date='9999-01-01') b
on  a.emp_no=b.emp_no
order by b.salary_now-a.salary_start