明确题意：

统计每个0级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和0分处理。

问题分解：

  • 内连接试卷作答表、试卷信息表和用户信息表: exam_record JOIN examination_info USING(exam_id) JOIN user_info USING(uid)
  • 筛选0级用户、高难度试卷：WHERE level=0 and difficulty='hard'
  • 按用户分组：GROUP BY uid
  • 计算平均得分，未完成时0分处理：
    • AVG(COALESCE(score, 0)) as avg_score
    • 保留为整数：ROUND(x, 0)
  • 计算平均用时，未完成时最长用时：
    • AVG(IFNULL(TimeStampDiff(MINUTE, start_time, submit_time), duration))
    • 保留1位小数：ROUND(x, 1)

细节问题：

  • 表头重命名：as

完整代码：

SELECT uid, ROUND(AVG(COALESCE(score, 0)), 0) as avg_score,
    ROUND(AVG(
        IFNULL(TimeStampDiff(MINUTE, start_time, submit_time), duration)
    ), 1) as avg_time_took
FROM exam_record
LEFT JOIN examination_info USING(exam_id)
LEFT JOIN user_info USING(uid)
WHERE `level`=0 and difficulty='hard'
GROUP BY uid;