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

select 
    t2.exam_id  ,
    t2.duration ,
    t2.release_time 
from (
    select 
        t1.exam_id      ,
        t1.duration     ,
        t1.release_time ,
        t1.time_diff 
    from
        (
        select 
            tb.exam_id      ,
            tb.duration     ,
            tb.release_time ,
            timestampdiff(minute, ta.start_time, ta.submit_time)                        as time_diff ,  # 时间间隔
            row_number() over(partition by ta.exam_id order by 
                            timestampdiff(minute, ta.start_time, ta.submit_time) desc ) as rank1,  # 倒序
            row_number() over(partition by ta.exam_id order by 
                            timestampdiff(minute, ta.start_time, ta.submit_time) )      as rank2   # 升序
        from      exam_record as ta
        left join examination_info as tb 
            on tb.exam_id = ta.exam_id
        where timestampdiff(minute, ta.start_time, ta.submit_time) is not null  # 试卷要提交
        ) as t1
    where rank1 = 2 or rank2 = 2  # 取第二快或第二慢
    ) as t2
group by t2.exam_id
having ( (max(t2.time_diff) - min(t2.time_diff) ) >= ( t2.duration / 2) )
order by t2.exam_id desc
/*SQL*/;