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

select
    a1.emp_no,
    a1.max_salary - a2.min_salary growth
from (
    select
        emp_no,
        salary max_salary,
        from_date max_from_date
    from salaries
    where to_date = '9999-01-01'
) a1
inner join (
    select 
        t1.emp_no,
        t2.salary min_salary,
        t2.from_date min_from_date
    from employees t1
    inner join salaries t2
    on t1.emp_no = t2.emp_no and t1.hire_date = t2.from_date
) a2
on a1.emp_no = a2.emp_no
order by a1.max_salary - a2.min_salary;