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

select
    t1.exam_id,
    ei.duration,
    ei.release_time  
from (
    select
        exam_id,
        row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) rk1,
        row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) asc) rk2,
        timestampdiff(second,start_time,submit_time) time
    from exam_record
    where score is not null
)t1
join examination_info ei on t1.exam_id = ei.exam_id
group by t1.exam_id
having (max(if(rk1=2,t1.time,0) - if(rk2=2,t1.time,0)))/60 > ei.duration/2
order by t1.exam_id desc