薪资涨幅情况

数据：https://blog.csdn.net/qq_38560351/article/details/106331612
题目：
1. 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

SELECT (
(
SELECT salary from salaries 
WHERE 1=1 
and emp_no=10001
order by to_date DESC 
LIMIT 1)  -(
SELECT salary from salaries 
WHERE 1=1 
and emp_no = 10001
order by to_date ASC 
LIMIT 1) 
) as growth;

2. 查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!=‘9999-01-01’，这样的数据不显示在查找结果里面）

SELECT aa.emp_no,(aa.salary-bb.salary) as growth
FROM (
SELECT a.emp_no,b.salary
FROM employees as a
JOIN salaries as b on a.emp_no = b.emp_no
WHERE 1=1
AND b.to_date = '9999-01-01') as aa
JOIN (
SELECT a.emp_no,b.salary
FROM employees as a
JOIN salaries as b on a.emp_no = b.emp_no
WHERE 1=1
AND b.from_date = a.hire_date) as bb on aa.emp_no = bb.emp_no
ORDER BY growth