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

SELECT er.uid,
        ROUND(SUM(IF(score IS NULL,0,er.score))/COUNT(*),0) AS avg_score,
        ROUND(SUM(IF(er.submit_time is NULL,t2.duration,TIMESTAMPDIFF(MINUTE,er.start_time,er.submit_time)))/COUNT(*),1) AS avg_time_took   
FROM exam_record er 
JOIN (
    SELECT uid
    FROM user_info
    WHERE level = 0
) AS t1
ON er.uid = t1.uid
JOIN (
    SELECT exam_id ,duration
    FROM examination_info
    WHERE difficulty = 'hard'
) AS t2
ON er.exam_id = t2.exam_id
GROUP BY er.uid;