不需要子查询的方法~

未完成的情况可以用IF来选择

IF(score, score, 0)  # 如果没有score就设为0
IF(submit_time, TIMESTAMPDIFF(minute, start_time, submit_time), duration)  
# 如果没有submit_time就设为duration=60分钟

最终代码

SELECT 
    uid,
    round(SUM(IF(score, score, 0)) / COUNT(uid), 0) avg_score,
    round(SUM(IF(submit_time, TIMESTAMPDIFF(minute, start_time, submit_time), duration)) / COUNT(uid), 1) avg_time_took
FROM exam_record
JOIN examination_info ei USING(exam_id)
JOIN user_info ui USING(uid)
WHERE ui.level = 0 AND ei.difficulty = 'hard'  # 每个0级用户所有的高难度试卷
GROUP BY uid