明确题意：

找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序

问题分解：

• 统计每套试卷第二快和第二慢的用时及试卷信息，生成子表 t_exam_time_took:
  • 统计每次完成试卷的用时及试卷信息，生成子表 t_exam_record_timetook:
    • 关联试卷作答表和试卷信息表：exam_record JOIN examination_info USING(exam_id)
    • 筛选完成了的试卷：WHERE submit_time IS NOT NULL
    • 统计作答用时：TimeStampDiff(SECOND, start_time, submit_time) / 60 as time_took
  • 计算第二慢用时，取按试卷分区耗时倒排第二名：
    • NTH_VALUE(time_took, 2) OVER (PARTITION BY exam_id ORDER BY time_took DESC) as max2_time_took
  • 计算第二快用时，取按试卷分区耗时正排第二名：
    • NTH_VALUE(time_took, 2) OVER (PARTITION BY exam_id ORDER BY time_took ASC) as max2_time_took
• 筛选第二快/慢用时之差大于试卷时长一半的试卷：WHERE max2_time_took - min2_time_took > duration / 2

细节问题：

• 表头重命名：as
• 按试卷ID降序排序：ORDER BY exam_id DESC

完整代码：

SELECT exam_id, duration, release_time
FROM (
    SELECT DISTINCT exam_id, duration, release_time,
        NTH_VALUE(time_took, 2) OVER (
            PARTITION BY exam_id ORDER BY time_took DESC) as max2_time_took,
        NTH_VALUE(time_took, 2) OVER (
            PARTITION BY exam_id ORDER BY time_took ASC) as min2_time_took
    FROM (
        SELECT exam_id, duration, release_time,
            TimeStampDiff(SECOND, start_time, submit_time) / 60 as time_took
        FROM exam_record JOIN examination_info USING(exam_id)
        WHERE submit_time IS NOT NULL
    ) as t_exam_record_timetook
) as t_exam_time_took
WHERE max2_time_took - min2_time_took > duration / 2
ORDER BY exam_id DESC;