做之前：简单来说，人分组，日期排序，用rk1+1=rk2，左连接

调试半天做完后，没考虑离职，没考虑排序

再捋一下，分组排序->每组的最大日期,链接薪水->最后薪水；同理，最初薪水；连接，相减，

除去离职的

这样来说最终薪水直接考虑to_date=99990101的人就行

select c.emp_no,(c.mxsalary-d.mnsalary) as growth
from
(select a.emp_no,b.salary as mxsalary
from
(select emp_no,max(from_date) as mx
from salaries
group by emp_no)a
join
salaries b
on a.emp_no=b.emp_no and a.mx=b.from_date)c
join
(select a.emp_no ,b.salary as mnsalary
from
(select emp_no,min(from_date) as mn
from salaries
group by emp_no)a
join
salaries b
on a.emp_no=b.emp_no and a.mn=b.from_date)d
on
c.emp_no=d.emp_no
where c.emp_no in
(select emp_no from salaries where to_date="9999-01-01")
order by growth

修改后

select c.emp_no,(c.mxsalary-d.mnsalary) as growth
from
(select emp_no,salary as mxsalary
from salaries where to_date="9999-01-01"
)c
join
(select a.emp_no ,b.salary as mnsalary
from
(select emp_no,min(from_date) as mn
from salaries
group by emp_no)a
join
salaries b
on a.emp_no=b.emp_no and a.mn=b.from_date)d
on
c.emp_no=d.emp_no
order by growth