WITH t1 AS (
SELECT exam_id, 
           MAX(CASE WHEN start_time IS NOT NULL THEN TIMESTAMPDIFF(MINUTE, start_time, submit_time) ELSE 0 END) AS max_time,
           MIN(CASE WHEN start_time IS NOT NULL THEN TIMESTAMPDIFF(MINUTE, start_time, submit_time) ELSE 0 END) AS min_time
    FROM exam_record
    GROUP BY exam_id
),
t2 AS (
    SELECT er.exam_id, TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS time_use
    FROM exam_record AS er INNER JOIN t1 ON er.exam_id = t1.exam_id
    WHERE submit_time IS NOT NULL AND TIMESTAMPDIFF(MINUTE, start_time, submit_time) <= max_time
    UNION ALL
    SELECT er.exam_id, TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS time_use
    FROM exam_record AS er INNER JOIN t1 ON er.exam_id = t1.exam_id
    WHERE submit_time IS NOT NULL AND TIMESTAMPDIFF(MINUTE, start_time, submit_time) >= min_time
),
t3 AS (
    SELECT ei.exam_id AS exam_id
    FROM t2 INNER JOIN examination_info AS ei ON ei.exam_id = t2.exam_id
    GROUP BY ei.exam_id, ei.duration
    HAVING MAX(time_use) - MIN(time_use) >= duration / 2
)

SELECT ei.exam_id, ei.duration, ei.release_time
FROM examination_info AS ei
WHERE ei.exam_id IN (SELECT exam_id FROM t3)
ORDER BY ei.exam_id DESC;

关于临时表1(t1)

WITH t1 AS (
    SELECT exam_id, 
           MAX(CASE WHEN start_time IS NOT NULL THEN TIMESTAMPDIFF(MINUTE, start_time, submit_time) ELSE 0 END) AS max_time,
           MIN(CASE WHEN start_time IS NOT NULL THEN TIMESTAMPDIFF(MINUTE, start_time, submit_time) ELSE 0 END) AS min_time
    FROM exam_record
    GROUP BY exam_id
)

该表作用：

筛选出各个exam_id的最大做题时间以及最小做题时间

关于临时表2(t2)

t2 AS (
    SELECT er.exam_id, TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS time_use
    FROM exam_record AS er INNER JOIN t1 ON er.exam_id = t1.exam_id
    WHERE submit_time IS NOT NULL AND TIMESTAMPDIFF(MINUTE, start_time, submit_time) <= max_time
    UNION ALL
    SELECT er.exam_id, TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS time_use
    FROM exam_record AS er INNER JOIN t1 ON er.exam_id = t1.exam_id
    WHERE submit_time IS NOT NULL AND TIMESTAMPDIFF(MINUTE, start_time, submit_time) >= min_time
)

该表作用：

筛选出 完成试卷 且 做题时间 ≤ 最大做题时间 的记录，这里相当于剔除了最大做题时间的记录，为t3中查找第二慢做准备

筛选出 完成试卷 且 做题时间 ≥ 最小做题时间 的记录，这里相当于剔除了最小做题时间的记录，为t3中查找第二快做准备

注意：

为什么过滤条件是要大于等于或者小于等于？

这里要避免：第一快和第二快做题时间一样， 第一慢和第二慢的做题时间一样

关于临时表3(t3)

t3 AS (
    SELECT ei.exam_id AS exam_id
    FROM t2 INNER JOIN examination_info AS ei ON ei.exam_id = t2.exam_id
    GROUP BY ei.exam_id, ei.duration
    HAVING MAX(time_use) - MIN(time_use) >= duration / 2
)

该表作用：

筛选出 第二慢和第二快的用时 ≥ 试卷时间/2 的exam_id

注意：

即使 第一快和第二快| 第一慢和第二慢 做题时间相同，在使用MIN和MAX时也可以保证准确的得到 第二快和第二慢

关于最终查询

使用t3得到的exam_id，在exam_record中筛选对应的记录

如果解题思路有任何问题，烦请您发评论，相互学习~