题解 | 计算薪资涨幅

with lay_off_t as 
    (select
        a.雇员编号,
        b.薪水
    from
        雇员表 a 
    left join
        薪水表 b on a.雇员编号 = b.雇员编号
    where 
        b.结束日期 = '2004-01-01'),
get_in_t as 
    (select
        a.雇员编号,
        b.薪水
    from
        雇员表 a
    left join 
        薪水表 b on a.雇员编号 = b.雇员编号 and a.雇用日期 = b.起始日期)
select
    l.雇员编号,
    l.薪水 - g.薪水 as 薪水涨幅
from    
    lay_off_t l 
inner join
    get_in_t g on l.雇员编号 = g.雇员编号