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

SELECT l3.exam_id,l3.duration, l3.release_time
FROM(
    SELECT l2.exam_id,l2.duration, l2.release_time, 
        SUM( CASE WHEN l2.max_rank=2 THEN l2.exam_duration
                    WHEN l2.min_rank=2 THEN -l2.exam_duration
                    ELSE 0
                    END
            ) AS duration_sub
    FROM(
        SELECT l1.exam_id,l1.exam_duration, i.duration, i.release_time,
            ROW_NUMBER() OVER (PARTITION BY l1.exam_id ORDER BY l1.exam_duration DESC) AS max_rank,
            ROW_NUMBER() OVER (PARTITION BY l1.exam_id ORDER BY l1.exam_duration ) AS min_rank
        FROM(
            SELECT exam_id, TIMESTAMPDIFF(SECOND, start_time, submit_time) AS exam_duration
            FROM exam_record
            WHERE submit_time IS NOT NULL
            ) AS l1
        LEFT JOIN examination_info AS i ON l1.exam_id=i.exam_id
        ) AS l2
    GROUP BY l2.exam_id
    ) AS l3
WHERE l3.duration_sub >= l3.duration*60*0.5
ORDER BY l3.exam_id DESC;