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

select T1.emp_no as emp_no, T2.salary-T1.salary as growth
from
(select # 每个员工的最早的薪水情况
    A.emp_no,
    salary
from
    salaries as A
    inner join ( # 最早的from_date
        select
            emp_no,
            min(from_date) as early
        from
            salaries
        group by
            emp_no
    ) as B on A.emp_no = B.emp_no and A.from_date = B.early
) as T1

inner join

(select # 每个员工的最晚的薪水情况
    A.emp_no,
    salary
from
    salaries as A
    inner join ( # 最晚的from_date
        select
            emp_no,
            max(from_date) as last
        from
            salaries
        where to_date = '9999-01-01' # 表示在职
        group by
            emp_no
    ) as B on A.emp_no = B.emp_no and A.from_date = B.last
) as T2

on T1.emp_no = T2.emp_no order by growth;