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

with first as(
select emp_no,salary as first_salary
from salaries
where (emp_no,from_date) in (select emp_no,min(from_date) from salaries group by emp_no)
),
second as (
    select emp_no,salary as now_salary
    from salaries
    where to_date = '9999_01-01'
)
select first.emp_no,now_salary-first_salary as growth
from first join second on first.emp_no = second.emp_no
order by growth

first表代表了所有员工入职时的初始薪资，second表代表了所有在职员工的当前薪资。

两张表通过emp_no连接生成所有在职员工的薪资情况表：emp_no,first_salary(初始薪资)，now_salary(当前薪资)

题目所求的薪资涨幅即为当前薪资-初始薪资