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

select t2.emp_no,(newsalary - oldsalary) growth from(
(select emp_no,salary as newsalary from salaries as t3 where to_date = "9999-01-01") as t1 ---在职员工现在的薪水
inner join 
(select emp_no,salary oldsalary    ---在职员工刚入职时的薪水
from(
select emp_no,salary,rank()over(partition by emp_no order by to_date) ranking  --按照员工id给日期排了个序
from salaries s1
where emp_no 
in (select emp_no from salaries s2
where to_date = "9999-01-01") 
) as t1
where ranking = 1)  ---选择最小的日期
  as t2 on t1.emp_no = t2.emp_no)
order by growth

评论区有的解法没看懂，我比较笨感觉写的有点长