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

【分析题目】:
第一个条件：在职员工，所以要先对员工进行筛选

【第一次代码】错误
select s.emp_no, max(s.salary) - min(s.salary) from salaries as s group by s.emp_no where s.emp_no = (select salaries.emp_no from salaries group by salaries.emp_no having max(to_date) = '9999-01-01')
直接出错跑不出来
【第二次代码】错误
select * from (select s.emp_no, max(s.salary)-min(s.salary) as growth from salaries as s group by s.emp_no) as t where t.emp_no in (select salaries.emp_no from salaries group by salaries.emp_no having max(to_date) = '9999-01-01') order by growth
预期输出：
10002|0
10005|16464
10003|27483
10001|28841
10007|31346
10004|34003
10009|34480
实际输出：
10002|0
10005|16464
10003|27871
10001|28841
10007|31346
10004|34003
10009|34480
【不知道为什么有个输出不同，感觉逻辑上没有问题】在讨论区逛了一会发现可能是存在降薪问题，我用的是最高工资减去最低工资，当一个员工先降薪后涨薪是就会出错，涨幅应该是当前工资减去入职工资。但是实际生活中我这样子写应该是没有问题的，降薪几乎不会出现吧。

【第三次代码】
select s1.emp_no, (s2.salary-s1.salary) as growth from
(select e.emp_no, s.salary from employees as e left join salaries as s on e.emp_no = s.emp_no and s.from_date = e.hire_date) as s1
inner join
(select e.emp_no, s.salary from employees as e left join salaries as s on e.emp_no = s.emp_no and s.to_date = '9999-01-01') as s2
on s1.emp_no = s2.emp_no
order by growth
预期输出：
10001|3861
实际输出：
10001|3861
10002|None
【原因】生成表s2是用了left join会导致目前不在职的员工也写入表中
【第四次代码】
select s1.emp_no, (s2.salary-s1.salary) as growth from
(select e.emp_no, s.salary from employees as e left join salaries as s on e.emp_no = s.emp_no and s.from_date = e.hire_date) as s1
inner join
(select e.emp_no, s.salary from employees as e join salaries as s on e.emp_no = s.emp_no and s.to_date = '9999-01-01') as s2
on s1.emp_no = s2.emp_no
order by growth
形成第二个表时用inner join就可以跑出来了

【下面是查看讨论区大佬的解法】
方法1：利用左外连接创建Scurrent表和Sstart表
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT e.emp_no, s.salary
FROM employees AS e
LEFT JOIN salaries AS s
ON e.emp_no=s.emp_no
WHERE s.to_date='9999-01-01') AS sCurrent
INNER JOIN (SELECT e.emp_no, s.salary
FROM employees AS e
LEFT JOIN salaries AS s
ON e.emp_no=s.emp_no
AND s.from_date=e.hire_date) AS sStart
ON sCurrent.emp_no=sStart.emp_no
ORDER BY growth ASC;

注意：方法1中红色的WHERE不能改成AND(这个问题本质上是将过滤条件放在ON和WHERE是否一致的问题)
当我们使用关联操作时，关联两张表或多张表来返回记录时，数据库就会生成一张临时表，最后将这张临时表返回给用户。以LEFT JOIN为例：在使用LEFT JOIN时，ON和WHERE的过滤条件的区别如下：
ON条件是在生成临时表时使用的条件，它不管ON中的条件是否为真，都会返回左边表中的记录
WHERE条件是在临时表已经生成后，对临时表进行的过滤条件。如果WHERE条件不为真的记录就会被过滤掉。
由于LEFT JOIN(以及RIGHT JOIN，FULL JOIN)的特殊性，不管ON条件是否为真，数据库都会返回左侧(或右侧、左右两侧)表中的全部记录。由于INNER JOIN没有这样的特殊性，所以过滤条件放在ON中或WHERE中，其返回的结果是一样的。
这是使用的是LEFT JOIN，而s.to_date=‘9999-01-01’是连接表之后需要进行的过滤条件，所以必须放在WHERE里面。
参考资料：SQL中的ON和WHERE的区别：https://blog.csdn.net/liitdar/article/details/80817957
补充：内层求工资表的时候，用的左外连接，是考虑有些新员工没有工资的实际情况。如果认为所有员工都有工资，则可以改成INNER JOIN。
补充：因为内连接也可以改成查两表+WHERE的形式，所以代码也可以改成：
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT e.emp_no, s.salary
FROM employees AS e
INNER JOIN salaries AS s
ON e.emp_no=s.emp_no
WHERE s.to_date='9999-01-01') AS sCurrent,
(SELECT e.emp_no, s.salary
FROM employees AS e
INNER JOIN salaries AS s
ON e.emp_no=s.emp_no
AND s.from_date=e.hire_date) AS sStart
WHERE sCurrent.emp_no=sStart.emp_no
ORDER BY growth ASC;
补充：工资表的构建甚至不需要用到employees表
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT emp_no, salary
FROM salaries
WHERE to_date='9999-01-01') AS sCurrent,
(SELECT s.emp_no, s.salary
FROM salaries AS s
WHERE s.from_date=(SELECT from_date
FROM salaries
WHERE emp_no=s.emp_no
ORDER BY from_date ASC LIMIT 1)
) AS sStart
WHERE sCurrent.emp_no=sStart.emp_no
ORDER BY growth ASC;
疑惑：不知道为什么，改成下面这个样子，代码会错误，我想可能是测试数据有点问题。
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.emp_no, s.salary
FROM salaries AS s
WHERE s.to_date=(SELECT to_date
FROM salaries
WHERE emp_no=s.emp_no
ORDER BY to_date DESC LIMIT 1)
) AS sCurrent,
(SELECT s.emp_no, s.salary
FROM salaries AS s
WHERE s.from_date=(SELECT from_date
FROM salaries
WHERE emp_no=s.emp_no
ORDER BY from_date ASC LIMIT 1)
) AS sStart
WHERE sCurrent.emp_no=sStart.emp_no
ORDER BY growth ASC;

方法2：连续内连接
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (employees AS e
INNER JOIN salaries AS sCurrent
ON e.emp_no=sCurrent.emp_no
AND sCurrent.to_date='9999-01-01'
INNER JOIN salaries AS sStart
ON e.emp_no=sStart.emp_no
AND sStart.from_date=e.hire_date
)
ORDER BY growth ASC;
注意：方法中标红的两个AND都不可以被替换成WHERE，即这两个AND都是ON意义下的，虽然在INNER JOIN中ON和WHERE的意义相同，但是在连续内连接中，不能出现WHERE(原因推测而只用ON的话，相当于只是生成临时表？)
注意：这种写法并不常见，不是很建议

错误示范：错误点：连续内连接不是这样写的。这样写第一个SELECT到WHERE查出来一个表和第二个表内连接，什么都没查出来(语法报错)
SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM employees AS e
INNER JOIN salaries AS sCurrent
ON e.emp_no=sCurrent.emp_no
WHERE sCurrent.to_date='9999-01-01'
INNER JOIN salaries AS sStart
ON e.emp_no=sStart.emp_no
WHERE sStart.from_date=e.hire_date
ORDER BY growth ASC;

错误示范：错误点：在非相关子查询的情况下引用外部表
SELECT a.emp_no, (b.salary - c.salary) AS growth
FROM employees AS a,
(SELECT emp_no, salary
FROM salaries
WHERE to_date ='9999-01-01') AS b,
(SELECT emp_no, salary
FROM salaries
WHERE from_date =a.hire_date) AS c
WHERE a.emp_no=b.emp_no
AND a.emp_no=c.emp_no
ORDER BY growth ASC;
注意：要引用外部查询的表，多数情况下是在WHERE里面进行的相关子查询，或者SELECT子句里(可见23题方法2)
补充：
SQL相关子查询和非相关子查询：参考资料：https://blog.csdn.net/shiyong1949/article/details/80923083
非相关子查询的执行不依赖与外部的查询：
执行过程为：
(1)：执行子查询：其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。
(2)：执行外部查询，并显示整个结果
相关子查询的执行依赖于外部查询：多数情况下是子查询的WHERE子句中引用了外部查询的表。
执行过程：
(1)：从外层查询中取出一个元祖，将元祖相关的值传给内层查询。
(2)：执行内层查询，得到子查询操作的值。
(3)：外查询根据子查询返回的结果或者结果集得到满足条件的行
(4)：然后外层查询取出下一个元祖重复做步骤1-3，直到外层的元祖全部处理完毕。
总结：
非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕之后将值传递给外部查询。
相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。
所以非相关子查询比相关子查询效率高。

下面是我认为正确的利用子查询两种写法，虽然无法通过，但在mysql的部分测试中通过了？
SELECT DISTINCT a.emp_no, (a.salary - b.salary) AS growth
FROM salaries AS a, salaries AS b
WHERE a.salary=(SELECT salary
FROM salaries AS s
WHERE s.emp_no=a.emp_no
ORDER BY to_date DESC LIMIT 1)
AND b.salary=(SELECT salary
FROM salaries AS s
WHERE s.emp_no=a.emp_no
ORDER BY to_date ASC LIMIT 1)
AND a.emp_no=b.emp_no
ORDER BY growth ASC;
在mysql和部分测试数据中的结果：

SELECT DISTINCT emp_no,
(SELECT salary
FROM salaries
WHERE emp_no=a.emp_no
ORDER BY to_date DESC LIMIT 1)
-
(SELECT salary
FROM salaries
WHERE emp_no=a.emp_no
ORDER BY to_date ASC LIMIT 1) AS growth
FROM salaries as a
ORDER BY growth;
在mysql和部分测试数据中的结果：

我认为是因为有些员工在employees表的hire_date不等于他在salaries表中的from_date。