题解 | #第二快/慢用时之差大于试卷时长一半的试卷#

# 1.按照exam_id分组计算用时最多和用时最少
# 2.排除用时最多和用时最少后,再分组,再计算最慢-最快(用时最多-用时最少)
# 3.试卷时长化成秒,用时也用秒表示
# 最慢-最快(用时最多-用时最少)>duration*60/2=duration*30
select exam_id,duration,release_time
from exam_record
left join 
(select exam_id
,max(timestampdiff(second,start_time,submit_time)) as maxtp
,min(timestampdiff(second,start_time,submit_time)) as mintp
,duration,release_time
from exam_record
left join examination_info using(exam_id)
where submit_time is not null
group by exam_id) as tt using(exam_id)
where submit_time is not null
and timestampdiff(second,start_time,submit_time) not in (tt.maxtp,tt.mintp)
group by exam_id
having (max(timestampdiff(second,start_time,submit_time))
-min(timestampdiff(second,start_time,submit_time)))>duration*30
order by exam_id desc