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

select t1.exam_id as exam_id,
    t3.duration as duration,
    t3.release_time as release_time
from
(
    select uid,
        exam_id,
        TIMESTAMPDIFF(SECOND, start_time, submit_time) as time1,
        row_number() over(partition by exam_id order by TIMESTAMPDIFF(SECOND, start_time, submit_time) asc) as rk_time_asc
    from exam_record
) as t1 
left join(
    select uid,
        exam_id,
        TIMESTAMPDIFF(SECOND, start_time, submit_time) as time2,
        row_number() over(partition by exam_id order by TIMESTAMPDIFF(SECOND, start_time, submit_time) desc) as rk_time_desc
    from exam_record
) as t2
on t1.exam_id=t2.exam_id
and t1.rk_time_asc=t2.rk_time_desc
left join examination_info as t3
on t3.exam_id=t1.exam_id
where t1.rk_time_asc=2
and ((t2.time2-t1.time1)/60)>(t3.duration/2)
order by exam_id desc