题解 | #查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
http://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
- 只使用salaries表
select distinct x.emp_no
,(select x1.salary from salaries x1 where x1.emp_no=x.emp_no and to_date='9999-01-01')
-(select x3.salary from salaries x3 where x3.emp_no=x.emp_no and
to_date=(select min(to_date) from salaries x2 where x2.emp_no=x.emp_no))
as growth
from salaries x
where emp_no in (select distinct emp_no from salaries where to_date='9999-01-01')
order by growth
- 使用两个表，用from_date=hire_date判断入职salary
select distinct x.emp_no
,(select x1.salary from salaries x1 where x1.emp_no=x.emp_no and to_date='9999-01-01')-
(select x3.salary from salaries x3 where x3.emp_no=x.emp_no
and x3.from_date=(select hire_date from employees e where e.emp_no=x.emp_no))
as growth
from salaries x
where emp_no in (select distinct emp_no from salaries where to_date='9999-01-01')
order by growth
