题解 | #查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
http://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
-- 是最早和最晚薪资的差额
with t1 as (
select emp_no,salary,
row_number() over (partition by emp_no order by from_date ) as rn_e,
row_number() over (partition by emp_no order by from_date desc ) as rn_l
from employees
left join salaries
using(emp_no)
where emp_no in (select emp_no from salaries where to_date='9999-01-01')) #在职员工
select emp_no, (t3.salary-t2.salary) as growth
from (select emp_no,salary from t1 where rn_e=1) as t2
join (select emp_no,salary from t1 where rn_l=1) as t3
using(emp_no)
order by growth
with t1 as (
select emp_no,salary,
row_number() over (partition by emp_no order by from_date ) as rn_e,
row_number() over (partition by emp_no order by from_date desc ) as rn_l
from employees
left join salaries
using(emp_no)
where emp_no in (select emp_no from salaries where to_date='9999-01-01')) #在职员工
select emp_no, (t3.salary-t2.salary) as growth
from (select emp_no,salary from t1 where rn_e=1) as t2
join (select emp_no,salary from t1 where rn_l=1) as t3
using(emp_no)
order by growth
