薪水表中记录了员工的编号，所在部门编号，和薪水。
查询出每个部门除去最高、最低薪水后的平均薪水，并保留整数。（字节跳动面试题）
【解题步骤】
1.如何找出最高、最低薪水？
要求每个部门除去最高、最低薪水后的的平均薪水，所以应该查询出每个部门的最高、最低工资。
所以需要按每组来排名薪水，既要排名，又要分组的问题，需要用窗口函数来解决。
窗口函数的基本语法如下：
-
<窗口函数> over (partition by
<用于分组的列名>
-
order by
<用于排序的列名>)
语法中<窗口函数>的位置，可以放以下两种函数：
1） 专用窗口函数，包括rank, dense_rank, row_number等专用窗口函数。
2） 聚合函数，如sum. avg, count, max, min等
在该题中，我们需要对部门进行分组，并对薪水进行排序：
-
select *,
-
rank()
over (
partition
by 部门编号
-
order
by 薪水
desc)
as ranking
-
from 薪水表;
查询结果如下，因为是降序排列，排名为1的是每个部门的最高薪水。
我们还需要再用一次窗口函数求出每个部门的最低薪水，也就是升序排列时，排名为1的是每个部门的最低薪水
-
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（蓝色框）的是每个部门的最低薪水。
2.如何去掉最高和最低薪水？
用where子句来筛选就可以了，where rank_1 > 1 and rank_2 > 1
-
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的运行顺序，会先运行from和where子句，最后才运行select子句。
而rank_1和rank_2在select子句中，是最后运行的。所以运行时where子句中的rank_1和rank_2是不存在的。
所以，我们需要将前面的查询结果作为子查询后，再用where子句：
-
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;
查询结果如下，此时已经是每个部门去掉最高、最低薪水后的结果了。
3.查询每个部门除去最高、最低薪水的平均薪水
看到“每个”这样的问题，要想到用分组（group by），平均薪水使用avg函数。
另外，题目还要求薪水保留整数。保留整数即保留0位小数，可以用format函数：
-
format(N,D)
-
N是要格式化的数字
-
D是要舍入的小数位数。
-
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.部门编号;
查询结果如下。
【本题考点】
1.考察解决复杂问题的能力，可以使用逻辑树分析方法，将复杂问题拆解问简单的子问题。
2.考查sql的运行顺序和子查询
3.遇到既要分组，又要排名的问题，要想到使用窗口函数
4.考查平均数的计算以及结果保留几位小数
【举一反三】
如图是某班6名同学的成绩：
请你写一个sql语句查询该6名同学的成绩中除去最高、最低分的后的平均分数，并保留2位小数。
-
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;
转载：https://blog.csdn.net/zhongyangzhong/article/details/112343029