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

select distinct rk.exam_id,duration,release_time
from
(
    select exam_id,
    nth_value(timestampdiff(second,start_time,submit_time),2)over(partition by exam_id order by timestampdiff(second,start_time,submit_time)) kuai_2,
    nth_value(timestampdiff(second,start_time,submit_time),2)over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) man_2
    from exam_record
) rk
left join examination_info ei
using(exam_id)
where man_2-kuai_2>duration*30
order by rk.exam_id desc