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

select
    t2.emp_no,
    salary2-salary1 growth
from
    (
        (
            select
                s.emp_no,
                s.salary salary1,
                from_date
            from
                salaries s
                left join employees e on s.emp_no = e.emp_no
            where
                hire_date = from_date
        ) as t1
        left join (
            select
                s.emp_no,
                s.salary salary2,
                to_date
            from
                salaries s
                left join employees e on s.emp_no = e.emp_no
            where
                to_date = '9999-01-01'
        ) as t2 on t1.emp_no = t2.emp_no
    ) 
where
    t1.emp_no in (
        select
            s.emp_no
        from
            salaries s
            left join employees e on s.emp_no = e.emp_no
        where
            to_date = '9999-01-01'
    )
order by growth