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

select
    exam_id,
    duration,
    release_time
from
    (
        select
            exam_id,
            duration,
            release_time,
            sum(
                case
                    when rank1 = 2 then timesub
                    when rank2 = 2 then - timesub
                    else 0
                end
            ) as sub
        from
            (
                select
                    er.exam_id as exam_id,
                    duration,
                    release_time,
                    timestampdiff (minute, start_time, submit_time) as timesub,
                    row_number() over (
                        partition by
                            exam_id
                        order by
                            timestampdiff (minute, start_time, submit_time) desc
                    ) as rank1,
                    row_number() over (
                        partition by
                            exam_id
                        order by
                            timestampdiff (minute, start_time, submit_time) asc
                    ) as rank2
                from
                    exam_record as er
                    inner join examination_info as ei on ei.exam_id = er.exam_id
                where
                    submit_time is not null
            ) as table1
        group by
            exam_id
    ) as table2
where
    sub * 2 >= duration
order by
    exam_id desc