oracle decode函数取代if else

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