题解 | #查找在职员工自入职以来的薪水涨幅情况#
查找在职员工自入职以来的薪水涨幅情况
https://www.nowcoder.com/practice/fc7344ece7294b9e98401826b94c6ea5
select
emp_no,
(now_salary - ago_salary) as growth
from
(select
emp_no,
sum(case when to_date = '9999-01-01' then salary else 0 end) as now_salary,
sum(case when from_date = hire_date then salary else 0 end) as ago_salary
from salaries natural join employees where emp_no in (
select emp_no from salaries where to_date = '9999-01-01'
)
group by emp_no
) as tmp
order by growth
1.首先求在职员工当前的薪资，和入职时候的薪资
select
emp_no,
sum(case when to_date = '9999-01-01' then salary else 0 end) as now_salary, -- 当前薪资
sum(case when from_date = hire_date then salary else 0 end) as ago_salary -- 入职时薪资
from salaries natural join employees where emp_no in (
select emp_no from salaries where to_date = '9999-01-01'
) -- 在职人员
group by emp_no -- 根据编号分组
2.然后在求出涨薪幅度，按照薪资涨幅升序
#担心入职之后被发现很菜怎么办#
