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

select er.uid,
       round(AVG(CASE WHEN score is null THEN 0 ELSE score END),0) AS avg_score,
       round(AVG(CASE WHEN submit_time is not null
        THEN (TIMESTAMPDIFF(MINUTE, start_time, submit_time))
        else duration END),1) AS avg_time_took
from exam_record as er
# left join
# (select uid,max((TIMESTAMPDIFF(MINUTE, start_time, submit_time))) as max_time
# from exam_record
# group by uid) as s using(uid)
这一块注释掉是因为理解错了题意 duration理解成了用户做试卷所出现过的最大时长 不过觉得自己真不错 恰巧还写出来了 所以留念一下
left join examination_info as ei using(exam_id)
left join user_info as ui using(uid)
where level=0 and difficulty='hard'
group by er.uid