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

SELECT uid,round(avg(case when score is not null then score else 0 end),0)as avg_score,
round(avg(case when score is not null then TIMESTAMPDIFF(MINUTE,start_time,submit_time) 
    else duration end),1)as avg_time_took
    from
(select r.exam_id,r.uid,r.start_time,r.submit_time,r.score from exam_record r
 left join user_info u on r.uid=u.uid where u.level=0)a 
right join (select exam_id,duration from examination_info where difficulty="hard")b
on a.exam_id=b.exam_id
GROUP by uid
order by avg_time_took desc