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

select uid, round(avg(score),0) as avg_score, round(avg(time),1) as avg_time_took from
(select er.uid, ei.exam_id,
(case when er.score is not null then er.score else 0 end ) as score, 
(case when er.submit_time is not null then timestampdiff(minute,start_time, submit_time) else ei.duration end ) as time 
from exam_record as er 
left join examination_info as ei on er.exam_id = ei.exam_id
where uid in 
(select uid from user_info
where level = 0)
and ei.exam_id in
(select exam_id from examination_info
where difficulty = 'hard')) as a
group by uid

最里层底表：

1. where uid in +where exam_id in 筛选等级为0的用户和难度为困难的考卷
2. case when 处理分数为空值 和提交时间为空值的试卷
3. 只需链接exam_record和exam_info

外层表

1. 完成两个avg汇总计算，group by uid