“雇员表“中记录了员工的信息,“薪水表“中记录了对应员工发放的薪水。两表通过“雇员编号”关联。查找当前所有雇员入职以来的薪水涨幅,给出雇员编号以及其对应的薪水涨幅,并按照薪水涨幅进行升序。
(注:薪水表中结束日期为2004-01-01的才是当前员工,否则是已离职员工)
-- 入职薪水
select a.雇员编号
,薪水 as last_salary
from 雇员表 a inner join 薪水表 b
on a.雇员编号 = b.雇员编号
where 雇用日期 = 起始日期
and 雇员编号 in (select 雇员编号 from 薪水表
where 结束日期 = '2004-01-01')
-- 计算薪资涨幅:
select t1.雇员编号
,(curr_salary - last_salary) as Salary_incre
from (select 雇员编号
,薪水 as curr_salary
from 薪水表
where 结束时间 = '2004-01-01') t1
inner join(
select a.雇员编号
,薪水 as first_salary
from 雇员表 a inner join 薪水表 b
on a.雇员编号 = b.雇员编号
where 雇用日期 = 起始日期
and a.雇员编号 in (select 雇员编号
from 薪水表 where 结束日期 = '2004-01-01'))t2
on t1.雇员编号 = t2.雇员编号
order by Salary_incre;