解题思路:
第1步:找出最早的入职日期,利用窗口函数
select DISTINCT emp_no,
min(from_date) over(PARTITION BY emp_no) as 'latest_date'
from salaries;
第2步:确定工资涨幅的标志,就是to_date='9999-01-01'
第3步:连表,涨幅后的工资-入职的工资='growth'
第4步:别忘了,按涨幅排序。。。。
总的代码:
select s1.emp_no,s3.salary-s1.salary as 'growth'
from salaries as s1
join (
select DISTINCT emp_no,
min(from_date) over(PARTITION BY emp_no) as 'latest_date'
from salaries
) as s2
on s1.emp_no=s2.emp_no and s1.from_date=s2.latest_date
join salaries as s3
on s1.emp_no=s3.emp_no
where s3.to_date='9999-01-01'
order by s3.salary-s1.salary asc;