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

思路: level=0, difficulty='hard' 进行过滤, 然后计算平均分, 和平均花费时间 这里要注意: 一般的聚合函数是不会计算null值的, 想要计算null值, 要不就用case when 构造新列, 要不就用if 进行判断, 如果时间是空值则取该试卷的duration字段即可. 非空值则使用timestampdiff计算 答题时间

select aa.uid,
    round(avg(if(aa.submit_time is null, 0, aa.score)), 0) avg_socre,
    round(avg(if(aa.submit_time is null, bb.duration, TIMESTAMPDIFF(minute, aa.start_time, aa.submit_time))), 1) avg_time_took
from exam_record aa
join examination_info bb on aa.exam_id=bb.exam_id
join user_info cc on aa.uid=cc.uid
where cc.level=0
and bb.difficulty='hard'
group by aa.uid