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

select t1.uid,round(avg(t1.score),0),round(avg(t1.submit_time),1) from (
select e2.uid,
(case when e2.submit_time is null then e1.duration else
 TIMESTAMPDIFF(minute,e2.start_time,e2.submit_time) end) as submit_time,
(case when e2.submit_time is null then 0 else e2.score end ) as score
from 
exam_record as e2 left join examination_info as e1 on e2.exam_id=e1.exam_id 
left join user_info as u1 on e2.uid=u1.uid where u1.level=0 and e1.difficulty='hard') t1 group by t1.uid