图解面试题：你有多久没涨过工资了？

【题目】

“雇员表 “中记录了员工的信息，“薪水表“中记录了对应员工发放的薪水。两表通过“雇员编号” 关联。

查找当前所有雇员入职以来的薪水涨幅，给出雇员编号以及其对应的薪水涨幅，并按照薪水涨幅进行升序。

（注：薪水表中结束日期为 2004-01-01 的才是当前员工，否则是已离职员工）

【解题思路】

要求出当前所有雇员入职以来的薪水涨幅，薪水涨幅 = 当前薪水 - 入职薪水。

所以，需要知道雇员的入职薪水和当前薪水。

当前薪水是 “薪水表 “中的 “结束日期”=2004-01-01，这一行对应的薪水。

入职薪水是 “雇员表 “中的 “雇用日期”=“薪水表 “中的 “起始日期”，这一行对应的薪水。

【解题步骤】

1. 当前薪水

当前薪水是 “薪水表 “中的 “结束日期”=2004-01-01。从 “薪水表 “给的案例数据可以看出，“雇员编号”（10002）有两条薪水记录，说明他经历过一次涨薪。“雇员编号”（10005）的薪水 “结束日期” 不是 2004-01-01，说明该员工已经离职。“雇员编号”（10006）有一条薪水记录，说明他没有经历过涨薪。

select 雇员编号,薪水 as 当前薪水
from 薪水表
where 结束日期 = '2004-01-01';

2. 入职薪水

入职薪水是 “雇员表 “中的 “雇用日期”=“薪水表 “中的 “起始日期”。

这涉及到两个表，因此需要用到多表联结，拿出《猴子从零学会 SQL》里面的多表联结图。

使用哪种联结呢？

因为题目要求的是 “查找当前所有雇员”，所以用“雇员表” 为左表，保留左表的全部数据，所以使用左联结。

select a.雇员编号,薪水 as 入职薪水
from 雇员表 as a 
left join 薪水表 as b
on a.雇员编号 = b.雇员编号
where a.雇用日期 = b.起始日期;

因为雇员表中还包含了离职员工，而题目要求的是 “当前所有雇员 “，也就是在职员工，所以需要用 where 子句筛出在职的员工。也就是结束日期 = '2004-01-01'的员工编号：

where a.雇员编号 in 
(select 雇员编号
from 薪水表
where 结束日期 = '2004-01-01');

将两表联结和 where 条件加入，完整的 sql 就是：

select a.雇员编号,薪水 as 入职薪水
from 雇员表 as a 
left join 薪水表 as b
on a.雇员编号 = b.雇员编号
where a.雇用日期 = b.起始日期 and a.雇员编号 in 
(select 雇员编号
from 薪水表
where 结束日期 = '2004-01-01');

3. 薪水涨幅

把步骤 1 的查找结果当做临时表 m，把步骤 2 的查询结果当做临时表 n。两表通过 “雇员编号” 进行多表联结。

使用哪种联结呢？

因为要保留左表（m）的全部数据（在职的全部雇员），所以使用左联结。

select m.雇员编号,当前薪水-入职薪水 as 薪水涨幅
from m
left join n
on m.雇员编号 = n.雇员编号;

4. 按薪水涨幅进行升序

使用 order by 子句对查询结果排序。把表 m 和表 n 的 sql 代码代入，得到：

select m.雇员编号,当前薪水-入职薪水 as 薪水涨幅
from
(select 雇员编号,薪水 as 当前薪水 
from 薪水表 
where 结束日期 = '2004-01-01') as m
left join 
(select a.雇员编号,薪水 as 入职薪水 
from 雇员表 as a 
left join 薪水表 as b 
on a.雇员编号 = b.雇员编号
where a.雇用日期 = b.起始日期 and a.雇员编号 in 
(select 雇员编号
from 薪水表
where 结束日期 = '2004-01-01')) as n
on m.雇员编号 = n.雇员编号 
order by 薪水涨幅;

‍

【本题考点】

考查理解业务的能力。知道如何将 “薪水涨幅 “指标定义为入职薪水 - 当前薪水。
考查多表联结。需要知道什么情况下使用哪种联结。

【举一反三】

查找所有学生开学以来的成绩涨幅，给出学生编号以及其对应的成绩涨幅，并按照成绩涨幅进行升序。

select m.学生编号,当前成绩-入学成绩 as 成绩涨幅
from
(select 学生编号,成绩 as 当前成绩 
from 成绩表 
where 结束日期 = '2011-10-02') as m
left join
(select a.学生编号,b.成绩 as 入学成绩 
from 学生表 as a 
left join 成绩表 as b 
on a.学生编号 = b.学生编号 
where a.入学日期 = b.起始日期) as n
on m.学生编号 = n.学生编号
order by 成绩涨幅;