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

select
    t2.exam_id,
    duration,
    release_time
from(select
        exam_id,
        sum(if(fast=2,time,null)) as 2nd_fastest,
        sum(if(slow=2,time,null)) as 2nd_slowest
        from(select
            exam_id,
            row_number() over(partition by exam_id order by timestampdiff(second,start_time, submit_time)) as fast,
            row_number() over(partition by exam_id order by timestampdiff(second,start_time, submit_time) desc) as slow,
            timestampdiff(second, start_time, submit_time) as time
        from exam_record) t1
    group by exam_id) t2
left join examination_info
using(exam_id)
where 2nd_slowest - 2nd_fastest > duration*60/2
order by exam_id desc