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

with temp as
(
select s.*,hire_date
from salaries s
join employees e
on e.emp_no=s.emp_no
),
t as
(
select *
,max(case when from_date=hire_date then salary end) start_sal
,max(case when to_date='9999-01-01' then salary end) end_sal
from temp
group by emp_no
)

select emp_no,end_sal-start_sal as growth
from t
where end_sal is not null
group by emp_no
order by growth