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

Select exam_id, duration, release_time
from examination_info
where exam_id in (
    Select exam_id
    from(
    Select exam_id, timediff(min(td),max(td)) as diff, duration
    from (
    Select exam_id , td, duration
    from (
    Select er.exam_id, uid, timediff(start_time, submit_time) as td, duration, 
    row_number() over(partition by er.exam_id order by timediff(start_time, submit_time) DESC ) as subtimeDESC_rank, row_number() over(partition by er.exam_id order by timediff(start_time, submit_time) ASC ) as subtimeASC_rank
    from exam_record er left join examination_info ei on er.exam_id = ei.exam_id
    where submit_time is not null 
    ) t1
    where  subtimeDESC_rank = 2 or subtimeASC_rank = 2 ) t2
    group by exam_id
    having minute(diff) >= duration/2 ) t3
)
    order by exam_id desc