用sql查询各部门除了最高最低工资外的平均工资.

1、现有工资表记录了员工编号、部门编号和工资。

2、解题思路：

1.先找出部门（表中有多个部门）中最高和最低工资，因此这里不仅要排名，还需要分组。所以考虑用窗口函数来解决。

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

<窗口函数> over partition by <用于分组的列名>

order by <用于排序的列名>

其中语法中<窗口函数>可以是专用窗口函数：rank,dense_rank,row_number等专用窗口函数，也可以是聚合函数，如：sum、avg、count、max和min等。

3、由于我们不仅要对部门进行分组，还需要对工资进行排序。用代码表示如下：

select *,

rank() over(partition by 部门编号

order by 工资 desc) as rank1

from salary;

以下图片为代码运行结果。其中结果中rank1列排名为1的为各部门最高工资。

4、这时还需要再用一次窗口函数，找出各部门的最低工资。用代码表示如下：

select *,

rank() over(partition by 部门编号

order by 工资 asc) as rank2

from salary;

5、为方便起见，将两个函数合并在一起，表示如下：

select *,

rank() over(partition by 部门编号

order by 工资 desc) as rank1,

rank() over(partition by 部门编号

order by 工资 asc) as rank2

from salary;

代码运行结果如下图：

6、但题目要求我们求各部门除最高和最低工资外的平均工资，因此还需要对以上结果进一步处理。也就是对以上结果中rank1=1和rank2=1的结果进行剔除，得到各部门中除了最高工资和最低工资的工资表。用代码表示如下：

select *

from (

select *,

rank() over(partition by 部门编号

order by 工资 desc) as rank1,

rank() over(partition by 部门编号

order by 工资 asc) as rank2

from salary)as a

where a.rank1>1 and rank2>1;

运行代码后，得到的结果显示如下图：

7、由于这里涉及到每个部门的问题，因此还需要对部门进行分组。用代码表示如下：

select a.部门编号,format(avg(工资),0)as 平均工资

from (

select *,

rank() over(partition by 部门编号

order by 工资 desc) as rank1,

rank() over(partition by 部门编号

order by 工资 asc) as rank2

from salary)as a

where a.rank1>1 and rank2>1

group by a.部门编号;

代码运行结果如下：