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

#第一种方法
# select a.uid,round(avg(if(score is null,0,score)),0) avg_score,round(avg(TRUNCATE((UNIX_TIMESTAMP(if(submit_time is null and b.tag='算法' ,DATE_ADD(start_time,INTERVAL 80 MINUTE),if(submit_time is null ,DATE_ADD(start_time,INTERVAL 60 MINUTE),submit_time)))-UNIX_TIMESTAMP(start_time))/60,0)),1) avg_time_took
# from 
# exam_record a 
# left join examination_info b
# on a.exam_id = b.exam_id
# left join user_info c 
# on c.uid = a.uid
# where c.level=0 and b.difficulty='hard' 
# group by c.uid
#第二种解法
select a.uid,round(avg(if(score is null,0,score)),0) avg_score,
round(avg(if(submit_time is not null, timestampdiff(minute, start_time, submit_time), duration)),1) avg_time_took
from 
exam_record a 
left join examination_info b
on a.exam_id = b.exam_id
left join user_info c 
on c.uid = a.uid
where c.level=0 and b.difficulty='hard' 
group by c.uid