select decode(count(*),0,0,1,0,2,0,(sum(a.smilemark)-max(a.smilemark)-min(a.smilemark))/(count(*)-2)) , count(*),b.c_name
from tfb_score_info a,t_hr_crew b
where a.markobj_user_code=b.p_code and a.smilemark is not null
and a.flight_date>=date'2019-4-1' and a.flight_date<date'2019-5-1'
group by a.markobj_user_code
select b.c_name,tab.avg_score from
(select ((sum(a.smilemark)-max(a.smilemark)-min(a.smilemark))/(count(*)-2)) as avg_score, count(*),a.markobj_user_code
from tfb_score_info a
where a.smilemark is not null
and a.flight_date>=date'2019-4-1' and a.flight_date<date'2019-5-1'
group by a.markobj_user_code
having count(*)>2) tab,t_hr_crew b where tab.markobj_user_code=b.p_codehaving count(*)>2) tab,t_hr_crew b where tab.markobj_user_code=b.p_code