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

select distinct exam_id, duration, release_time
from
    (select exam_id as exam_id, duration, release_time,
           sum(case when rank1 = 2 then costtime when rank2 = 2 then -costtime else 0 end) as sub
    from (
        select e_i.exam_id, duration, release_time,
        timestampdiff(minute, start_time, submit_time) as costtime,
        row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) desc) rank1,
        row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) asc) rank2
        from exam_record e_r join examination_info e_i
        on e_r.exam_id = e_i.exam_id
        where submit_time is not null
    ) table1 -- table1的作用是联结两个表并且排序
    group by exam_id
) table2  -- table2的作用是找出倒数第二个时间差
where sub * 2 >= duration  -- 最后一个查询找出符合要求的时间差并且排序
order by exam_id desc