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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiDEQnTrhXEuyfzLPb%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_1.webp?alt=media\&token=92b4b046-3cc5-4b5b-9d44-b4e569f5b8aa)

**【题目】**

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiDHXAfUTzqfu7scCA%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_2.webp?alt=media\&token=9dbfc665-8808-4bec-b96c-bbc0dfeb845a)

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

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

**【解题思路】**

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

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

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

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

**【解题步骤】**

**1. 当前薪水**

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiDLB2SuDAFP-rUuEX%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_3.webp?alt=media\&token=a641438a-0fe3-4191-ad02-735264017e4a)

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiDO9LxGPd7aifOXUV%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_4.webp?alt=media\&token=e95e70bf-97f0-49bb-8280-1308948596c4)

**2. 入职薪水**

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

这涉及到两个表，因此需要用到多表联结，拿出[《猴子 从零学会 SQL》](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649247566\&idx=2\&sn=5af748b677eb72028764dde0577675fb\&chksm=835fc77eb4284e68e8cfe3f08c5a671b9e080b2651f20b40b1c793ffda4042ae43ad8f35a755\&scene=21#wechat_redirect)里面的多表联结图。

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiDRFlqYpxu2j7jV07%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_5.png?alt=media\&token=ba7a723e-c2cb-4501-9ffd-0b08f8ba98ed)

使用哪种联结呢？

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiDU1qEEh8Gvubpzqg%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_6.webp?alt=media\&token=64060426-8dc6-478e-a67b-e8d7581ec968)

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiFPkGjNFtyoB-yDqK%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_6_1.png?alt=media\&token=3e1786eb-7866-40b9-a84f-a1d21db608c3)

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

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

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

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

```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');
```

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiDa-10YMBPIc877H6%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_7.webp?alt=media\&token=3108cb15-3d5c-46a4-838f-ff2df4aea242)

**3. 薪水涨幅**

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

使用哪种联结呢？

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiDf-RPcMI9Cj-5Jr_%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_8.png?alt=media\&token=b77c134f-702c-424d-8527-aff2653e261d)

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiDiPUumUOOsNeS7mI%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_9.webp?alt=media\&token=6702bafe-d1d0-4bab-a540-02377c4aa310)

**4. 按薪水涨幅进行升序**

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

```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 薪水涨幅;
```

‍

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiDm4b6_p5nKMN6RcF%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_10.webp?alt=media\&token=8c1ba06b-e2f4-49c2-b20a-bc467155ecfe)

**【本题考点】**

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

**【举一反三】**

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

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiE6yVEG5I7z5RoZAK%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_11.webp?alt=media\&token=2be5f539-af3a-49df-a072-bce7271536de)

```sql
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 成绩涨幅;
```

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiDygr2M-7edDJ8CHq%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_12.webp?alt=media\&token=b026bb01-a6ed-42c8-8b02-6a8002a6ac2d)

推荐：[如何从零学会 sql？](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649247566\&idx=2\&sn=5af748b677eb72028764dde0577675fb\&chksm=835fc77eb4284e68e8cfe3f08c5a671b9e080b2651f20b40b1c793ffda4042ae43ad8f35a755\&scene=21#wechat_redirect)

![](https://4109408004-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTuZ3PxkXf2a7UbRybW%2F-MUiD2rjc6_zNHCznDf3%2F-MUiEIfRbbIGIzTaERnh%2F%E4%BD%A0%E6%9C%89%E5%A4%9A%E4%B9%85%E6%B2%A1%E6%B6%A8%E5%B7%A5%E8%B5%84%E4%BA%86_13.webp?alt=media\&token=6c8714b2-5bd5-433d-b54f-9985070b18d9)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://houzidata.gitbook.io/sql/di-3-zhang-duo-biao-cha-xun/tu-jie-mian-shi-ti-ni-you-duo-jiu-mei-zhang-guo-gong-zi-le.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.