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

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.然后在求出涨薪幅度，按照薪资涨幅升序