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

只使用工资表

select now.emp_no,(now.salary-origin.salary) as growth from 
(select emp_no,salary from salaries
where to_date='9999-01-01') as now
left join 
(select emp_no,salary from salaries
group by emp_no
having to_date=min(to_date)) as origin
on now.emp_no=origin.emp_no
order by growth

now表to_date='9999-01-01'确保返回在职员工，由于每个id对应的to_date='9999-01-01'是唯一的，这里不需要group by分组。 所求得的即为现在工资

origin表对id分组求最早的to_date对应的工资，即为入职工资