题解 | #0级用户高难度试卷的平均用时和平均得分#
0级用户高难度试卷的平均用时和平均得分
https://www.nowcoder.com/practice/bb474c6cbd77478fb6d9fc86934d0ebb
多表联表查询最好用!
将三个表联表后看成一个表即可。
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;