题解 | 查找在职员工自入职以来的薪水涨幅情况
查找在职员工自入职以来的薪水涨幅情况
https://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
select
a.emp_no,
a.final_salary-b.initial_salary as growth
from
(select
t.emp_no,
s.salary as final_salary,
t.final_date
from
(select
emp_no,
max(to_date) as final_date
from salaries
group by emp_no) t
join salaries s on t.emp_no=s.emp_no and t.final_date=s.to_date) a
left join
(select
t.emp_no,
s.salary as initial_salary
from
(select
emp_no,
min(from_date) as initial_date
from salaries
group by emp_no) t
join salaries s on t.emp_no=s.emp_no and t.initial_date=s.from_date) b
on a.emp_no=b.emp_no
where a.final_date='9999-01-01'
order by growth asc
查看18道真题和解析