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

# 创建作答时间排名第二的CTE
WITH dier AS(
SELECT z.exam_id, z.duration, z.release_time, z.et
FROM(
    SELECT er.exam_id, ei.duration, ei.release_time, TIMESTAMPDIFF(minute, er.start_time, er.submit_time) AS et, # 计算作答时间
    ROW_NUMBER() OVER(PARTITION BY er.exam_id 
    ORDER BY TIMESTAMPDIFF(minute, er.start_time, er.submit_time)) AS ra
    FROM exam_record AS er
    LEFT OUTER JOIN examination_info AS ei
    ON er.exam_id = ei.exam_id
    WHERE er.score IS NOT NULL) AS z
WHERE z.ra = 2),
# 创建作答时间排名倒数第二的CTE
daoer AS (
SELECT m.exam_id, m.duration, m.release_time, m.et
FROM(
    SELECT er.exam_id, ei.duration, ei.release_time, TIMESTAMPDIFF(minute, er.       start_time, er.submit_time) AS et,
    ROW_NUMBER() OVER(PARTITION BY er.exam_id 
    ORDER BY TIMESTAMPDIFF(minute, er.start_time, er.submit_time) DESC) AS ra
    FROM exam_record AS er
    LEFT OUTER JOIN examination_info AS ei
    ON er.exam_id = ei.exam_id
    WHERE er.score IS NOT NULL) AS m
WHERE m.ra = 2)
# 运用表联结将排名第二和倒二的信息连接，并按条件筛选
SELECT dier.exam_id, dier.duration, dier.release_time
FROM dier 
INNER JOIN daoer
ON dier.exam_id = daoer.exam_id
WHERE  (daoer.et - dier.et) >= dier.duration / 2
ORDER BY dier.exam_id DESC;

结构化分解题目需求的思想，逻辑还是很清晰的，多多培养与锻炼此方法！