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

select
    a.emp_no,
    a.final_salary-b.initial_salary as growth

from

(select
t.emp_no,
s.salary as final_salary,
t.final_date
from
(select
emp_no,
max(to_date) as final_date
from salaries
group by emp_no) t
join salaries s on t.emp_no=s.emp_no and t.final_date=s.to_date) a

left join

(select 
t.emp_no,
s.salary as initial_salary
from
(select
emp_no,
min(from_date) as initial_date
from salaries
group by emp_no) t
join salaries s on t.emp_no=s.emp_no and t.initial_date=s.from_date) b

on a.emp_no=b.emp_no

where a.final_date='9999-01-01'

order by growth asc