牛客--SQL21.查找在职员工自入职以来的薪水涨幅情况

题目简单总结为：查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,

PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','2001-06-22');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1999-08-03');

INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');

我自己写的

1、找到入职时的工资a.salary

2、找到最后的工资b.salary

3、两表连接

select a.emp_no, (b.salary-a.salary) amount
from (
	select s.emp_no, s.salary
	from salaries s join employees e
	on s.emp_no = e.emp_no and s.from_date = e.hire_date) a
join(
	select s.emp_no, s.salary
	from salaries s join employees e
	on s.emp_no = e.emp_no and s.to_date = '9999-01-01') b
on a.emp_no = b.emp_no;

最后改动（简单化）

SELECT
	a.emp_no,
	( c.salary - b.salary ) AS growth 
FROM
employees a
	JOIN salaries b ON a.emp_no = b.emp_no AND b.from_date = a.hire_date -- 入职时的工资
	JOIN salaries c ON a.emp_no = c.emp_no AND c.to_date = "9999-01-01" -- 目前的工资