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

简单有效
select distinct exam_id,duration,release_time
from(
    select uid,exam_id,duration,release_time,
        Nth_value(timestampdiff(second,start_time,submit_time),2) over(partition by exam_id order by timestampdiff(second,start_time,submit_time) Rows between unbounded preceding and unbounded following  ) duration_asc_2,
        Nth_value(timestampdiff(second,start_time,submit_time),2) over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc Rows between unbounded preceding and unbounded following) duration_desc_2
    from exam_record
    join examination_info using(exam_id))n 
where duration_desc_2-duration_asc_2 > duration*30
order by exam_id desc