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

解题思路：

1.筛选高难度试卷,0级用户

2.平均用时，遇null为最大时长 round(sum(if(submit_time is null,duration,timestampdiff(minute,start_time,submit_time)))/count(uid),1)

3.平均得分，遇null为0 round(sum(if(score is null,0,score))/count(uid),0)

步骤：

1.筛选高难度试卷,0级用户

select 
a.uid
,c.duration
,b.start_time
,b.submit_time
,b.score
,b.exam_id
from user_info a
left join exam_record b
on a.uid=b.uid
left join examination_info c 
on c.exam_id=b.exam_id
where a.level=0
and c.difficulty='hard'

2.平均用时，遇null为最大时长

3.平均得分，遇null为0

select 
uid
,round(sum(if(score is null,0,score))/count(uid),0) avg_score
,round(sum(if(submit_time is null,duration,timestampdiff(minute,start_time,submit_time)))/count(uid),1) avg_time_took
from(
select 
a.uid
,c.duration
,b.start_time
,b.submit_time
,b.score
,b.exam_id
from user_info a
left join exam_record b
on a.uid=b.uid
left join examination_info c 
on c.exam_id=b.exam_id
where a.level=0
and c.difficulty='hard'
) tt
group by uid