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

select t2.e2 as emp_no,(t2.salary2-t1.salary1) as growth
from 
(select emp_no as e1,salary as salary1,rank()over(partition by emp_no order by from_date) as r from salaries) t1 join
(select emp_no as e2,salary as salary2 from salaries where to_date='9999-01-01') as t2 on t1.e1=t2.e2 
where t1.r=1
order by growth

想确定薪水涨幅，就需要找到入职时间的薪资、最新时间的薪资，以最新的薪资-入职时间的薪资，入职时间通过rank函数获得，最新时间由于要求在职，因此可以确认为‘9999-01-01’