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

from salaries s
left join
(select emp_no,salary as ms from salaries 
where to_date = '9999-01-01'
group by emp_no) T1         --求出当前在职人员工资
on T1.emp_no = s.emp_no
left join 
(select emp_no,salary as sa,
row_number() over(partition by emp_no order by to_date desc) as rn
from salaries             --用窗口函数，按离职时间倒序排序
where to_date <> '9999-01-01' --当时间不为9999-01-01时，倒序取第一个，就是上次的调薪时间，但这样会把不在职的也算进来
group by emp_no) T2
on T2.emp_no = s.emp_no
and T2.rn = 1                --取第一个
where s.to_date = '9999-01-01'  --由于会把不在职的也算进来，这里再用9999-01-01进行一下过滤
group by s.emp_no
order by growth
#sql#