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

select Ma.emp_no,(Ma.salary-Mi.salary) as growth from (select A.emp_no,B.salary from (select emp_no , MAX(to_date) as to_date from salaries group by emp_no) as A left join salaries as B on A.emp_no=B.emp_no and A.to_date=B.to_date where A.to_date='9999-01-01') as Ma, (select A.emp_no,B.salary from (select emp_no , MIN(to_date) as to_date from salaries group by emp_no) as A left join salaries as B on A.emp_no=B.emp_no and A.to_date=B.to_date) as Mi where Ma.emp_no = Mi.emp_no ORDER BY growth