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

SELECT exam_id
        ,duration
        ,release_time
FROM (
SELECT exam_id,
        duration,
        release_time,
        SUM(CASE WHEN ranking1 = 2 THEN -time WHEN ranking2 = 2 THEN time END) AS sub_time
FROM (
    SELECT uid
        ,exam_id
        ,duration
        ,release_time
        ,TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS time
        ,RANK() OVER(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time)) AS ranking1
        ,RANK() OVER(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) DESC) AS ranking2
    FROM exam_record
    JOIN examination_info USING(exam_id)
    WHERE submit_time IS NOT NULL
) AS time_sub_time
GROUP BY exam_id
) AS result
WHERE sub_time >= duration*0.5
ORDER BY exam_id DESC;

• 注意，这里应该使用ROW_numeber，这是因为它对应sum，即使存在多个相同第二名，或存在多个倒数第二名，也可以满足条件，取第一个就行，用户不影响考试，但是使用RANK则不一样，不能结合使用。