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

select uid,round(sum(if(score is null,0,score))/count(*),0) avg_score, 
round(sum(if(shijian is null,duration,shijian))/count(*),1) avg_time_look
from(
select t1.uid,t1.exam_id,timestampdiff(minute,t1.start_time,t1.submit_time) shijian,t1.score,duration
from exam_record t1
left join examination_info t2
on t1.exam_id = t2.exam_id
where t2.difficulty = "hard"
and t1.uid in (
    select uid
    from user_info
    where level = 0
)) t1
group by uid

注意这里将未完成的时长设置为了试卷总时长，未完成的分数设置为了0分