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

#方法一：失败了，可能是由于没有考虑到降薪的情况
#select 
#emp_no,
#maxs-mins growth
#from
#(
# select emp_no,
#max(salary) over(partition by emp_no) maxs,
#min(salary) over(partition by emp_no) mins,
#to_date
#from salaries

#) a
#where to_date='9999-01-01'
#order by growth

#方法二：成功了，用的表链接
select c.emp_no,
d.salary-c.salary growth
from
(
select emp_no,salary,to_date
from
(
 select *,
row_number() over(partition by emp_no order by from_date ) px1,
row_number() over(partition by emp_no order by from_date desc) px2
from salaries
)a
where px1=1
)c
inner join
(
select emp_no,salary,to_date
from
(
select *,
row_number() over(partition by emp_no order by from_date ) px1,
row_number() over(partition by emp_no order by from_date desc) px2
from salaries
 )b
 where px2=1 and to_date='9999-01-01'
)d
on c.emp_no=d.emp_no
order by growth