题解 | #0级用户高难度试卷的平均用时和平均得分#

SELECT uid, round(avg(score), 0)as avg_score, round(avg(took), 1)as avg_time_took from ( select exam_record.uid, exam_record.exam_id, if(submit_time is null, examination_info.duration, TIMESTAMPDIFF(MINUTE, start_time, submit_time)) as took, if(score is null, 0, score) as score from exam_record join examination_info on exam_record.exam_id = examination_info.exam_id join user_info on exam_record.uid = user_info.uid where user_info.level = 0 and examination_info.difficulty = 'hard') table1 group by uid