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

WITH
    a1 AS (
    SELECT a.exam_id,
           tag,
           difficulty,
           duration,
           release_time,
           TIMESTAMPDIFF(MINUTE,start_time,submit_time)                                               AS useing,
           ROW_NUMBER() OVER (PARTITION BY tag ORDER BY TIMESTAMPDIFF(MINUTE,start_time,submit_time)) AS '最快'
    FROM examination_info a JOIN exam_record b USING (exam_id)
    ),
    a2 AS (
    SELECT a.exam_id,
           tag,
           difficulty,
           duration,
           release_time,
           TIMESTAMPDIFF(MINUTE,start_time,submit_time)                                                    AS useing,
           ROW_NUMBER() OVER (PARTITION BY tag ORDER BY TIMESTAMPDIFF(MINUTE,start_time,submit_time) DESC) AS '最慢'
    FROM examination_info a JOIN exam_record b USING (exam_id)
    )
SELECT a1.exam_id, a1.duration, a1.release_time
FROM a1, a2
WHERE a1.最快 = 2
  AND a2.最慢 = 2
  AND a1.tag = a2.tag
  AND a1.difficulty = a2.difficulty
  AND ABS(a1.useing - a2.useing) > a1.duration / 2
ORDER BY a1.exam_id DESC;