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

首先筛选出每个在职（“在职”作为where筛选）员工最早日期（用rank函数对每个人关于日期作顺序排序后取第一位）的工资也就是初始工资，即

select * from
(select emp_no 
,salary
,rank()over(partition by emp_no order by to_date) asc_rk
from salaries
where emp_no in
(select emp_no
from salaries
where to_date='9999-01-01')) as asc_1
where asc_rk=1

同样的操作，筛选出每个在职（“在职”作为where筛选）员工现在日期（用rank函数对每个人关于日期作逆序排序后取第一位）的工资也就是现在工资，即

select * from
(select emp_no 
,salary
,rank()over(partition by emp_no order by to_date desc) desc_rk
from salaries
where emp_no in
(select emp_no
from salaries
where to_date='9999-01-01')) as desc_1
where desc_rk=1

再将两个表联合起来，这样每个员工就对应两个工资，一个是初始工资，一个是现在工资，再对每个员工用最大值减去最小值得到工资涨幅（如果考虑到降薪的情况，就用lag函数对rk排序求出上一个rk的工资，再相减，最后筛选出非空值。这里就不给出答案了）

select emp_no
,max(salary)-min(salary) growth
from ((select * from
(select emp_no 
,salary
,rank()over(partition by emp_no order by to_date) asc_rk
from salaries
where emp_no in
(select emp_no
from salaries
where to_date='9999-01-01')) as asc_1
where asc_rk=1)
union
(select * from
(select emp_no 
,salary
,rank()over(partition by emp_no order by to_date desc) desc_rk
from salaries
where emp_no in
(select emp_no
from salaries
where to_date='9999-01-01')) as desc_1
where desc_rk=1)) as new
group by emp_no
order by growth