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

select
ed.uid,
round(
avg(case when submit_time is null then 0 else  ed.score end), #如果submit_time 为空的话，则值为0，进行平均值计算
0),
round(
avg(case when submit_time is null then ex.duration else 
    timestampdiff(minute,ed.start_time,ed.submit_time)     #如果submit_time 为空的话，则值为最大时长计算，进行平均值计算
    end),1)
from exam_record ed left join user_info u1 
on u1.uid=ed.uid
left join examination_info ex on ex.exam_id=ed.exam_id
where
u1.level=0 
and
ex.difficulty="hard"
group by ed.uid