SQL37 0级用户高难度试卷的平均用时和平均得分

题目主要信息：

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

问题拆分：

• 筛选出每个0级用户高难度题的得分及耗时：
  • 得分信息、用户信息、题目信息分布三个表格中，我们用exam_id将exam_record和examination_info连在一起，再通过uid连上user_info。知识点：join...on...
  • 从连接后的表格中筛选出用户等级为0试题难度为hard的信息。知识点：where
  • 修改得分为空的分数为0。if(score is not null, score, 0) as new_score 知识点：if
  • 计算用户做这份试卷的用时，没有提交时间就设置为试卷限制时间。if(submit_time is not null, timestampdiff(minute, start_time, submit_time), duration) as cost_time 知识点：if、timestampdiff
  • 筛选出来的信息记为new_table
• 从new_table中筛选出每个用户的平均得分及平均用时，要以uid分组统计。知识点：group by、round()、avg()

代码：

select uid,
       round(avg(new_score), 0) as avg_score,
       round(avg(cost_time), 1) as avg_time_took
from(
    select e_r.uid as uid,
           if(score is not null, score, 0) as new_score,
           if(submit_time is not null, timestampdiff(minute, start_time, submit_time), duration) as cost_time
    from exam_record e_r join examination_info e_i
    on e_r.exam_id = e_i.exam_id
    join user_info u_i
    on e_r.uid = u_i.uid
    where level = 0
    and difficulty = 'hard'
    ) new_table
group by uid