牛客SQL题——查找所有员工入职以来的薪水
题目描述
有一个员工表employees简况如下:
employees
有一个薪水表salaries简况如下:
salaries
请你查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序，以上例子输出为
（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面，以上emp_no为2的就是这样的）
查询结果
解题思路
本来这题我最开始的思路是找到最大薪水和最低薪水相减，特喵的没想到测试用例里面还有一个人降薪降到比起薪还低，而这道题求得是现在的薪水和入职时的薪水的差值。错误思路代码如下：
select A.en,A.growth from
(
-- 在筛选条件为todate=‘99990101’的条件下，找出，职员编号，最大薪水最低薪水的差值，
select s.emp_no as en,
(max(s.salary)-min(s.salary)) as growth,
max(s.to_date)as k
from salaries s
group by s.emp_no
having k='99990101'
order by growth
) as A
测试样例错误情况
错误原因因为在中有一个月薪水降到了1000块，比起薪低，所以这种思路是错的。
后来想了很久，思考出来一种和牛客题解中都不太一样，利用自连接的解题方法：
-- 筛选
select A.aa,A.af growth from
-- 内连接，删选两个表的emp_no相等且todate=‘99990101’，即在职的
(
select a.emp_no aa,
-- a.salary ab,
-- a.from_date ac,
-- b.salary ad,
-- b.to_date ae,
(b.salary-a.salary) af,
(a.from_date-b.to_date) ag
from salaries as a join salaries as b on a.emp_no=b.emp_no
where b.to_date='99990101'
order by ag
) A
group by A.aa
order by growth
注意：上述代码中外层的group by没有结合聚集函数，是因为group by函数如果不加聚集函数的话选取的就是分组后的第一行数据，相当于是加了一个limit 1，而内层的数据我们可以看到如下图所示：
内层的输出
这里group by (limit 1),取得就是最上面那个ag差值最小（因为是用入职时间-离职时间，所以反过来是在职时间最长）的数据。
这道题调试过程中给我的启示
- 一定要添加上order by语句，否则你有时候搞不清楚默认的索引排序方式，会造成代码阅读起来的混乱。
- 巧用自连接能够解决很多查询的问题。
在mysql中扩展了这样的功能：
首先对于不加聚合函数的sql来说，它的功能结合了limit来得出结果，仔细想想的时候有点Oracle分析函数的味道，limit的时候得到的并不是 最大最小的值，而是某一下group by结果集中第一行，也就是刚才说的相当与先group by， 然后在每个group by下面进行limit 1。
