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

#在职，自入职以来涨薪幅度=当前工资-入职工资

with useful_e as
(
select
*
from employees
where emp_no in (select emp_no from salaries where to_date='9999-01-01')
)#只对employees保留在职的员工记录
, useful_s as
(
select
*
from salaries
#where (emp_no,to_date) in (select emp_no, hire_date from useful_e )。二刷这里s要用入职日期来比对，用成from_date了
where (emp_no,from_date) in (select emp_no, hire_date from useful_e )
or to_date='9999-01-01' 
)#只保留入职工资记录和当前工资记录

#放在同行运算，偏移函数
,compare as
(
select
*
,lag(salary,1)over() as t1 #取上一行是因为此时表中只剩2行，而取上之后能用9999-01-01来筛选
from useful_s
)

select
emp_no
,salary-t1 as growth
from compare 
where to_date='9999-01-01'
order by growth asc