题解 | 查找在职员工自入职以来的薪水涨幅情况
查找在职员工自入职以来的薪水涨幅情况
https://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
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(当前薪资)
题目所求的薪资涨幅即为当前薪资-初始薪资
查看19道真题和解析