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

select emp_no,growth from(select emp_no,to_date,(salary-lag(salary,1)over(partition by emp_no order by to_date)) growth from
(select emp_no,salary,to_date,rank()over(partition by emp_no order by to_date)r1,rank()over(partition by emp_no order by to_date desc)r2 
from salaries)t1 where (r1=1 or r2=1))t2 where to_date='9999-01-01'
order by growth

用了三个窗口函数+三个子查询。。回头看看别人的题解有没有好方法

法二：利用hire_date=from_date表连接筛选出初始薪水表t1，再用to_date='9999-01-01'筛选出在职人员的最终薪水表t2,内连接两张表剔除离职人员

select t1.emp_no emp_no,(s2-s1) growth from(select e.emp_no emp_no,salary s1 from employees e join salaries s on e.emp_no=s.emp_no and hire_date=from_date)t1 
join (select emp_no,salary s2 from salaries where to_date='9999-01-01')t2 on t1.emp_no=t2.emp_no
order by growth