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

select 
    T1.e_m,
    T2.salary_now - T1.salary_pre growth
from
    (select
        s1.salary salary_pre,
        s1.emp_no e_m
    from 
        employees e1
        inner join salaries s1
        on e1.emp_no = s1.emp_no 
        and e1.hire_date = s1.from_date
    ) T1
    inner join
    (select
        s2.salary salary_now,
        e2.emp_no e_m
    from 
        employees e2
        inner join salaries s2
        on e2.emp_no = s2.emp_no 
        and s2.to_date = '9999-01-01'
    ) T2
    on T1.e_m = T2.e_m
order by growth;