题解 | #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 null then duration
                else TIMESTAMPDIFF (MINUTE, start_time, submit_time)
            end
        ),
        1
    ) AS avg_time_took
FROM
    exam_record er
    JOIN examination_info ei USING (exam_id)
    JOIN user_info ui USING (uid)
WHERE
    ei.difficulty = "hard"
    AND ui.level = 0
GROUP BY
    er.uid;