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

select new3.emp_no as emp_no, new3.maxsalary - new2.minsalary as growth
from
(select new1.emp_no, salaries.salary as minsalary
from
(select salaries.emp_no as emp_no, min(from_date) as mindate
from (select emp_no
from salaries
where to_date = '9999-01-01') as new left join salaries on new.emp_no = salaries.emp_no
group by salaries.emp_no) as new1 join salaries on new1.emp_no = salaries.emp_no and new1.mindate = salaries.from_date) as new2
join
(select emp_no, salary as maxsalary from salaries 
where to_date = '9999-01-01') as new3
on new2.emp_no = new3.emp_no
order by growth