计算薪资涨幅,由于涉及到日期和链表和涨幅,例子比较典型,分享给大家
这里的思路就是,用两个子查询,一个查询入职时的薪资表,一个查询当前的薪资表,再将两个表连接,进行涨幅的计算
select t1.emp_no emp_no,(t2.salary-t1.salary) growth
from (
select e.emp_no emp_no,s.salary salary
from employees e
join salaries s
on e.hire_date=s.from_date
and e.emp_no=s.emp_no
) t1
join (
select e.emp_no emp_no,s.salary salary
from employees e
join salaries s
on e.emp_no=s.emp_no
where s.to_date='9999-01-01'
) t2
on t1.emp_no=t2.emp_no
order by growth