select uid, cast(avg(score_took) as signed) as avg_score, round(avg(time_took),1) as avg_time_took from (select ui.uid,er.exam_id, ifnull(timestampdiff(minute,start_time,submit_time),duration) as time_took, ifnull(score,0) as score_took from exam_record er join examination_info ei on er.exam_id=ei.exam_id join user_info ui on er.uid = ui.uid where level =0 and difficulty = 'hard') a group by uid
先在a层用ifnull解决空值,再在外层聚合计算