SQL面试题挑战12：计算部门的平均工资

SQL面试题挑战12：计算部门的平均工资

问题

问题：现在要计算每个部门的平均工资（工资和/员工数），但是要去掉部门的最高工资和最低工资（如果一个部门最高或最低工资有并列的，去掉一个最高的和一个最低的）后，计算部门的平均工资。

user_id,dept,salary
101,研发部,50000
102,研发部,50000
103,研发部,10000
104,研发部,20000
105,研发部,30000
106,市场部,20000
107,市场部,30000
108,产品部,20000
109,产品部,30000
110,产品部,25000

建表语句

drop table if exists salary;
create table salary(
    user_id varchar(20),
    dept varchar(10),
   salary decimal(16,2)
);
insert into salary values
(101,'研发部',50000),
(102,'研发部',50000),
(103,'研发部',10000),
(104,'研发部',20000),
(105,'研发部',30000),
(106,'市场部',20000),
(107,'市场部',30000),
(108,'产品部',20000),
(109,'产品部',30000),
(110,'产品部',25000)

解答

思路：

通过开窗排名，过滤掉人数，然后分组求均值即可

完整查询如下

select
    dept,
    avg(salary)avg_salary
from
    (
    select
         dept,
         salary,
         row_number() over (partition by dept order by salary)rk1,
         row_number() over (partition by dept order by salary desc)rk2
     from
         salary
    )t
where
    rk1 != 1 and rk2!=1
group by
    dept

注：这样查询如果部门只有两个人就不会显示该部门；因为如果只有两个人显示也没有意义，题目没有指明要不要显示这里就没有特殊处理。