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

from examination_info ei
join(
select distinct exam_id,start_time,submit_time,
row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time)) fast_2,
row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) slow_2
from exam_record
where submit_time is not null) a
on ei.exam_id = a.exam_id
where fast_2 = 2 and slow_2 = 2
and (slow_2-fast_2)*2 > duration 
order by ei.exam_id desc