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

SELECT uid, ROUND(AVG(score),0) AS avg_score, ROUND(AVG(time_took),1) AS avg_time_took
FROM (
  SELECT er.uid, 
  CASE WHEN submit_time IS NOT NULL THEN TIMESTAMPDIFF(MINUTE, start_time, submit_time) ELSE duration END AS time_took, 
  CASE WHEN score IS NOT NULL THEN score ELSE 0 END AS score
  FROM exam_record AS er LEFT JOIN examination_info AS ei ON er.exam_id = ei.exam_id
  LEFT JOIN user_info AS ui ON er.uid = ui.uid
  WHERE level = 0 AND difficulty = 'hard'
) AS t1
GROUP BY uid

① 对于t1表的思路与解释：

创建t1表的时，首先将三张表进行左连接，并筛选 用户是0级 且 试卷难度为hard 的记录

SELECT子句得到uid，两个条件判断语句：

1. 如果submit_time非空，那么就返回submit_time - start_time的差值(以分钟为单位)， 注意TIMESTAMPDIFF只适用于DATE类型的数据，以及会向下(即59秒也会被记为0分钟)；若submit_time为空，则返回duration

2. 当score非空，那么就返回score，其余则返回0

② 对建立的t1表取score的平均和time_took的平均，并使用ROUND进行四舍五入。