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

with tmp as(
         select exam_id,
           timestampdiff(second,start_time,submit_time)  times ,
            row_number()over(partition by exam_id order by   (submit_time -start_time) desc)  rk,
             row_number()over(partition by exam_id order by   (submit_time -start_time) )  rk2
        from exam_record
        )
select 
    t3.exam_id,
    duration,
    release_time
from 
    (
        select 
            exam_id,
            t1.times -t2.times times
        from 
            (
                select 
                     exam_id,
                     times
                from tmp
                where rk=2 
            )t1
            left join 
            (
                select 
                     exam_id,
                     times
                from tmp
                where rk2=2 
            )t2
            using(exam_id)

           ) t3
           join examination_info t4
           using (exam_id)
where times> duration
 order by  t3.exam_id desc