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

select a.exam_id,b.duration,b.release_time
from
(select a.exam_id,a.secondfast,b.secondslow,(b.secondslow-a.secondfast) as timeinterval
from
(select exam_id,time as secondfast
from
 (
select exam_id,time,rank() over (partition by exam_id
order by time asc) as rankk
from
(
select exam_id,timestampdiff (second, start_time, submit_time) as time
from exam_record) a
) b
where rankk = 2) a
left join 
(select exam_id,time as secondslow
from
(select exam_id,time,rank() over (partition by exam_id order by time desc) as rankk
from
(select exam_id,timestampdiff (second, start_time, submit_time) as time
from exam_record) a
) b
where rankk = 2
) b
on a.exam_id = b.exam_id) a
left join
examination_info b
on a.exam_id = b.exam_id
where timeinterval > duration*30
order by a.exam_id desc;