select (sum(a)-max(a)-min(a))/(count(a)-2) from ...
写个手工的吧! --先把最大和最小的剔除,然后求平均 --注意不能求max,min,防止重复 create table test( col num);select avg(num) from ( select num,rownum rn2 from( select num,rownum rn from test t order by num) where rn<>1 order by num desc) where rn<>1
select (sum(a)-max(a)-min(a))/(count(a)-2) from ... 这个是最好的办法了
这个也行,刚试过 select avg(col) from tem where not exists (select max(col) from tem union select min(col) from tem)
刚刚搞错了,正确的应该是这样的!select avg(col) from tem where col not in (select max(col) from tem union select min(col) from tem)
select (sum(a)-max(a)-min(a))/(count(a)-2) from ... 这个是最好的办法了 支持一下
用select (sum(a)-max(a)-min(a))/(count(a)-2) from ... 这个办法 如果这个数据集合里 刚好只有两个数,除数就变为0了.所以上面的sql必须保证count(a)>2
--先把最大和最小的剔除,然后求平均
--注意不能求max,min,防止重复
create table test(
col num);select avg(num) from (
select num,rownum rn2 from(
select num,rownum rn from test t order by num)
where rn<>1
order by num desc)
where rn<>1
这个是最好的办法了
select avg(col) from tem where not exists
(select max(col) from tem
union select min(col) from tem)
(select max(col) from tem
union select min(col) from tem)
这个是最好的办法了
支持一下
如果这个数据集合里 刚好只有两个数,除数就变为0了.所以上面的sql必须保证count(a)>2
除的时候判断下就好了,这方法相当的强