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

SELECT distinct exam_id, duration, release_time
FROM
# 用dense_rank()基于每种类型试卷排序，获得最短答题时间和最短时间排名
(SELECT 
    exam_id,
    time_spend fasttime,
    DENSE_RANK()OVER(PARTITION BY exam_id ORDER BY time_spend) fast
FROM(
    SELECT 
        exam_id, 
        timestampdiff(minute, start_time, submit_time) time_spend
    FROM exam_record er
) t1) t11
JOIN
# 用dense_rank()基于每种类型试卷排序，获得最长答题时间和最长时间排名
(SELECT 
    exam_id,
    time_spend slowtime,
    DENSE_RANK()OVER(PARTITION BY exam_id ORDER BY time_spend DESC) slow
FROM(
    SELECT 
        exam_id, 
        timestampdiff(minute, start_time, submit_time) time_spend
    FROM exam_record er
) t2) t21
    USING(exam_id)
JOIN examination_info 
    USING(exam_id)
# 筛选出时间最短的第二名和时间最长的第二名
# 利用时间差的条件进一步筛选
WHERE fast = 2 and slow = 2 and (slowtime - fasttime) > (duration)/2
ORDER BY exam_id DESC