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

SELECT DISTINCT exam_id,duration,release_time
FROM(
    SELECT exam_id,duration,release_time,
    SUM(CASE WHEN rank1 = 2 THEN time WHEN rank2 = 2 THEN -time ELSE 0 END) AS sub
    FROM(
        SELECT a.exam_id,duration,release_time,
        TIMESTAMPDIFF(minute,start_time,submit_time) as time,
        row_number()OVER(PARTITION BY a.exam_id ORDER BY TIMESTAMPDIFF(minute,start_time,submit_time)DESC) AS rank1,
        row_number()OVER(PARTITION BY a.exam_id ORDER BY TIMESTAMPDIFF(minute,start_time,submit_time)) AS rank2
        FROM exam_record a
        LEFT JOIN examination_info b
        ON a.exam_id = b.exam_id
        WHERE submit_time IS NOT NULL
    )t1
    GROUP BY exam_id
)t2
WHERE sub*2 >= duration
ORDER BY exam_id DESC

# ### 时间差TIMESTAMPDIFF(minute,start_time,submit_time) as time
# ### 排序第二快：row_number()OVER(PARTITION BY exam_id ORDER BY time DESC) AS rank1
# ### 排序第二慢：row_number()OVER(PARTITION BY exam_id ORDER BY time) AS rank2
# SELECT a.exam_id,duration,release_time,
# FROM exam_record a
# LEFT JOIN examination_info b
# ON a.exam_id = b.exam_id
# WHERE submit_time IS NOT NULL

# SUM(CASE WHEN rank2 = 2 THEN time THEN WHEN rank1 = 2 THEN -time ELSE 0 END) AS sub