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

SELECT DISTINCT
    exam_id,
    duration,
    release_time
FROM
    (
        SELECT
            exam_id,
            duration,
            release_time,
            SUM(
                CASE
                    WHEN rank1 = 2 THEN ABS(costtime)
                    WHEN rank2 = 2 THEN - ABS(costtime)
                    ELSE 0
                END
            ) as sub
        FROM
            (
                SELECT
                    exam_id,
                    duration,
                    release_time,
                    TIMESTAMPDIFF (minute, start_time, submit_time) as costtime,
                    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
                    JOIN examination_info USING (exam_id)
                WHERE
                    submit_time IS NOT NULL
            ) a
        GROUP BY
            exam_id
    ) b
WHERE
    sub * 2 >= duration
ORDER BY
    exam_id DESC;