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

# 先找到仍在职员工的最开始薪水和最后一次调整后的薪水、
with t1 as (
    select c.emp_no, c.start_salary, d.salary as end_salary
    from salaries as d
    right join (
        select a.emp_no, b.salary as start_salary, end_date
        from salaries as b
        right join (
            select emp_no, min(to_date) as first_date, max(to_date) as end_date
            from salaries
            where emp_no in (select emp_no from salaries where to_date = '9999-01-01')
            group by emp_no
        ) as a
        on a.emp_no = b.emp_no
        and a.first_date = b.to_date
    ) as c
    on c.emp_no = d.emp_no
    and c.end_date = d.to_date
)
select emp_no, end_salary - start_salary as growth
from t1
order by growth;