select 员工id,(total - max_score - min_score) / 6 as avg
from
(select 员工id,max(score) as max_score,min(score) as min_score,sum(score) as total
from tb
group by 员工id)
from
(select 员工id,max(score) as max_score,min(score) as min_score,sum(score) as total
from tb
group by 员工id)
insert into tb values('1',100);
insert into tb values('1',100);
insert into tb values('1',80);
insert into tb values('1',90);
insert into tb values('1',60);
insert into tb values('1',70);
insert into tb values('1',80);
insert into tb values('1',80);
insert into tb values('1',60);
select * from tb
select id,(total - max_score - min_score) / c as avg
from
(select id,max(score) as max_score,min(score) as min_score,sum(score) as total,
count(*) - 2 as c
from tb
group by id)
select id , ( sum( score ) - max( score ) - min( score ) ) / count( 1 )
from tb group by id ;
select id , ( sum( score ) - max( score ) - min( score ) ) / ( count( 1 ) - 2 )
from tb group by id having count(1) > 2 ;
测试:
create table tb (id varchar(10),score number);
insert into tb values('1',100);
insert into tb values('1',100);
insert into tb values('1',80);
insert into tb values('1',90);
insert into tb values('1',60);
insert into tb values('1',70);
insert into tb values('1',80);
insert into tb values('1',80);
insert into tb values('1',60);select id,(total - max_score - min_score) / c as avg
from
(select id,max(score) as max_score,min(score) as min_score,sum(score) as total,
count(*) - 2 as c
from tb
group by id) ;/* 结果
ID AVG
---------- ----------
1 80已选择 1 行。*/
(
select id,max(score) maxscore,min(score) min,sum(score) sumscore,count(1) cnt
from tablename
group by id
having count(1)>2
)