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

提供两种解题思路。

1.将每相邻时间段的工资差值相加。通过子查询选出两个新salary表s1,s2，这两个表中只记载salaries表中在职人的信息。将s1的to_date和s2的from_date匹配连接两表，这样就可以使得同一行里s1.salary是s2.salary的上一个时间段的值。然后根据emp_no分组以确保是将同一个人的信息进行匹配。从现在的表中选出emp_no和相邻时间段工资差之和，最后按该工资差之和排序。

select s1.emp_no, sum(s2.salary - s1.salary) as growth
from 
(select * from salaries 
 where emp_no in (select emp_no from salaries
 where to_date = str_to_date("9999-01-01","%Y-%m-%d"))) as s1,
(select * from salaries 
 where emp_no in (select emp_no from salaries
 where to_date = str_to_date("9999-01-01","%Y-%m-%d"))) as s2
 where s1.to_date = s2.from_date
 group by s1.emp_no
 order by growth

2.直接获取入职工资和当前工资的差值。使用两次salaries表并命名为s1,s2，通过子查询获得每个员工在salaries表中的一个时间段的结束日期并将表命名为get_min。通过emp_no员工号来连接s1,s2,get_min三表，并限制s1的to_date为"9999-01-01"，也就是在职状态，而s2的to_date则为第一个时间段的结束日期。通过这样的匹配我们就可以获得s1.salary-s2.salary，也就是当前的工资和第一个时间段工资的差值。

select s1.emp_no, s1.salary-s2.salary as growth 
from salaries as s1, salaries as s2,
(select emp_no, min(to_date) as min_date from salaries
group by emp_no) as get_min
where s1.emp_no=s2.emp_no and s1.emp_no=get_min.emp_no and s2.emp_no=get_min.emp_no
and s1.to_date="9999-01-01" and s2.to_date=get_min.min_date 
order by growth