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

第一步：建一个包含emp_no和salary的在职员工临时表 a （这个表里的员工的salary都是最新的salary）

select emp_no,salary as last_salary
from salaries
where to_date = '9999-01-01'

第二步：建一个包含emp_no和salary的员工临时表b（这个表里的员工的salary都是刚入职时的员工salary）

select emp_no,salary as first_salary
from salaries
where (emp_no,from_date) in (select emp_no,min(from_date)
                                from salary
                                group by emp_no)

第三步：联结两个表

select a.emp_no,(a.last_salary-b.first_salary) as growth
from (select emp_no,salary as last_salary
from salaries
where to_date = '9999-01-01') a
left join 
(select emp_no,salary as first_salary
from salaries
where (emp_no,from_date) in (select emp_no,min(from_date)
                                from salaries
                                group by emp_no))b
on a.emp_no=b.emp_no
order by growth