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

--关键点：当前仍在职、第一笔薪水和当前薪水
with a as (
    select t1.emp_no,t1.salary as first_salary --第一笔薪水
    from salaries t1 left join employees t2
    on t1.emp_no=t2.emp_no
    where t1.from_date=t2.hire_date
),
b as (
    select t1.emp_no,t1.salary as last_salary --当前薪水，这一步同时也筛选出当前在职的员工
    from salaries t1
    where t1.to_date='9999-01-01'
)
select 
    b.emp_no,
    (b.last_salary - a.first_salary) as growth
from b left join a 
on b.emp_no = a.emp_no
order by growth