# 字节跳动面试题：你的平均薪水是多少？

![](/files/-MWPVWhX91VFKKCDZfDN)

薪水表中记录了员工的编号，所在部门编号，和薪水。

![](/files/-MWPVasYFtAbnEt0BsoN)

查询出每个部门除去最高、最低薪水后的平均薪水，并保留整数。（字节跳动面试题）

**【解题步骤】**

**1. 如何找出最高、最低薪水？**

要求每个部门除去最高、最低薪水后的的平均薪水，所以应该查询出每个部门的最高、最低工资。

所以需要按每组来排名薪水，**既要排名，又要分组的问题**，需要用[窗口函数](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)来解决。

窗口函数的基本语法如下：

```sql
<窗口函数> over (partition by <用于分组的列名>                
order by <用于排序的列名>)
```

语法中 <窗口函数> 的位置，可以放以下两种函数：

1） 专用窗口函数，包括 rank, dense\_rank, row\_number 等专用窗口函数。

2） 聚合函数，如 sum. avg, count, max, min 等

在该题中，我们需要对部门进行分组，并对薪水进行排序：

```sql
select *,   
      rank() over (partition by 部门编号 
                   order by 薪水 desc) as ranking
from 薪水表;
```

查询结果如下，因为是降序排列，排名为 1 的是每个部门的最高薪水。

![](/files/-MWPVnNrYqjOHji33MZF)

我们还需要再用一次窗口函数求出每个部门的最低薪水，也就是升序排列时，排名为 1 的是每个部门的最低薪水

```sql
select *,   
  rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
  rank() over (partition by 部门编号 order by 薪水) as rank_2
from 薪水表;
```

查询结果如下。下图 rank\_1 列数值为 1（红色框）的是每个部门的最高薪水，rank\_2 列数值为 1（蓝色框）的是每个部门的最低薪水。

![](/files/-MWPVw_6cd6uOqG0puQb)

**2. 如何去掉最高和最低薪水？**

用 where 子句来筛选就可以了，where rank\_1 > 1 and rank\_2 > 1

![](/files/-MWPW2uh52zO7xRN1lya)

```sql
select *,   
  rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
  rank() over (partition by 部门编号 order by 薪水) as rank_2
from 薪水表
where rank_1 >1 and rank_2 >1;
```

但是这样运行会出错，因为按照 [sql 的运行顺序](http://mp.weixin.qq.com/s?__biz=MzU5NTc1ODI5Mw==\&mid=2247484035\&idx=1\&sn=f6cc38ffb91396061ab7f101257262db\&chksm=fe6c5f39c91bd62f88162ea5ef75a9b757139cad11f02ca6f3de2db931cc89e621921da0dc2d\&scene=21#wechat_redirect)，会先运行 from 和 where 子句，最后才运行 select 子句。

而 rank\_1 和 rank\_2 在 select 子句中，是最后运行的。所以运行时 where 子句中的 rank\_1 和 rank\_2 是不存在的。

所以，我们需要将前面的查询结果作为[子查询](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)后，再用 where 子句：

```sql
select *
from (
  select *,
  rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
  rank() over (partition by 部门编号 order by 薪水) as rank_2
  from 薪水表
 ) as a
where a.rank_1 >1 and a.rank_2 >1;
```

查询结果如下，此时已经是每个部门去掉最高、最低薪水后的结果了。

![](/files/-MWPWDYg9acK_-MdhY3O)

**3. 查询每个部门除去最高、最低薪水的平均薪水**

看到 “每个” 这样的问题，要想到用分组（group by），平均薪水使用 avg 函数。

另外，题目还要求薪水保留整数。保留整数即保留 0 位小数，可以用 format 函数：

```sql
format(N,D)
N是要格式化的数字
D是要舍入的小数位数。
```

```sql
select a.部门编号,format(avg(a.薪水),0) as 平均薪水
from
  (
  select *,
  rank() over (partition by 部门编号 order by 薪水 desc) as rank_1,
  rank() over (partition by 部门编号 order by 薪水) as rank_2
  from 薪水表
  ) as a
where a.rank_1 >1 and a.rank_2 >1
group by a.部门编号;
```

查询结果如下。

![](/files/-MWPWPog9cLfyTPvbexi)

**【本题考点】**

1. 考察解决复杂问题的能力，可以使用[逻辑树分析方法](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649248793\&idx=2\&sn=1fe86f3083bd9c8ad1a427898199c71c\&chksm=835fdc29b428553fabf47f23dc05f8f6c296cbcc06ce7e409ff712e81141fbf9ad0aa9b2b99d\&scene=21#wechat_redirect)，将复杂问题拆解问简单的子问题。
2. 考查 sql 的运行顺序和[子查询](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)
3. 遇到既要分组，又要排名的问题，要想到使用[窗口函数](http://mp.weixin.qq.com/s?__biz=MzAxMTMwNTMxMQ==\&mid=2649249645\&idx=2\&sn=d1295b268eff974fe52a2c0f8bdadccb\&chksm=835fdf5db428564b7d42f733cdd45c6ce1aeeff0da8cdfac2f5047354bc815895f1a7fd282de\&scene=21#wechat_redirect)
4. 考查平均数的计算以及结果保留几位小数

**【举一反三】**

如图是某班 6 名同学的成绩：

![](/files/-MWPWSrT046pWSnitxmL)

请你写一个 sql 语句查询该 6 名同学的成绩中除去最高、最低分的后的平均分数，并保留 2 位小数。

```sql
select format(avg(a.成绩),2) as 平均成绩
from
  (
  select *,
  rank() over (order by 成绩 desc) as rank_1,
  rank() over (order by 成绩) as rank_2
  from 成绩表
  ) as a
where a.rank_1 >1 and a.rank_2 >1;
```

![](/files/-MWPWchkurjOLl0ZxUo_)

![](/files/-MWPWgRx9F3HlAQFaht-)

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

![](/files/-MWPWkrmBPhmhfcVfHvY)


---

# 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-5-zhang-xiang-mu-shi-zhan/zi-jie-tiao-dong-mian-shi-ti-ni-de-ping-jun-xin-shui-shi-duo-shao.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.