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

1. 只使用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

2. 使用两个表，用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