思路是先求出最大和最小薪水值,用over函数变成两列,之后再从这个结果集中取出来薪水不是最大最小的即可

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