本题知识点 case when 和if函数的应用
select t5.uid,round(avg(score),0),round(avg(took),1) from (select t4.uid uid, case when score is null then 0 else score end as score, if(submit_time is null,duration,timestampdiff(minute,start_time,submit_time)) as took from (select t1.uid uid,start_time,submit_time,duration,score from exam_record t1 join examination_info t2 on t1.exam_id=t2.exam_id join user_info t3 on t1.uid=t3.uid where level=0 and difficulty='hard' ) t4 ) t5 group by t5.uid;