题解 | #0级用户高难度试卷的平均用时和平均得分#
0级用户高难度试卷的平均用时和平均得分
https://www.nowcoder.com/practice/bb474c6cbd77478fb6d9fc86934d0ebb
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
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
