计算平均值时去掉最大值和最小值

with dataset as (
    select ‘Jack‘ as Name,100 as Sales from dual
    union all
    select ‘Tom‘ as Name,120 as Sales from dual
    union all
    select ‘Jimmy‘ as Name,10 as Sales from dual
    union all
    select ‘Frank‘ as Name,999 as Sales from dual
    union all
    select ‘John‘ as Name,105 as Sales from dual
)

select avg(Sales)
from 
(
    select Name,
            Sales,
            min(Sales)over() as min_sales,
            max(Sales)over() as max_sales
    from dataset
) t
where t.Sales not in (t.min_sales,t.max_sales)