题解 | #查找排除当前最大、最小salary之后的员工的平均工资avg_salary#

题目描述：查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资

方案1：过滤max(salary),min(salary)，求平均

SELECT AVG(salary) AS avg_salary
FROM salaries
WHERE to_date = '9999-01-01' 
            AND salary NOT IN (SELECT MAX(salary) FROM salaries
                               WHERE to_date = '9999-01-01'
                               UNION
                               SELECT MIN(salary) FROM salaries
                               WHERE to_date = '9999-01-01');

或

select avg(a.salary) avg_salary
from salaries a
where a.to_date='9999-01-01'
and a.salary not in (select max(b.salary) from salaries b where b.to_date='9999-01-01')
and a.salary not in (select min(b.salary) from salaries b where b.to_date='9999-01-01');

参考来源：https://blog.nowcoder.net/n/7f7738ed8d924ddcb5e66e6ab869fe02?f=comment

或

select
avg(salary) avg_salary
from
(select 
salary
,max(salary) over() max_salary
,min(salary) over() min_salary
from
salaries
where to_date = '9999-01-01' ) a
where salary != max_salary and salary != min_salary

参考来源：https://blog.nowcoder.net/n/64302553d5f04cfa9538d1e509e98564?f=comment

方案2：采用聚合函数(不严谨，若max(salary),min(salary)不止一个会报错

SELECT (SUM(salary)-MAX(salary)-MIN(salary))/(COUNT(1)-2) AS avg_salary
FROM salaries
WHERE to_date = '9999-01-01';

参考来源：https://blog.nowcoder.net/n/2450ea8ca19f49b08079c980d4698c9a?f=comment