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

SELECT
aa.eid as exam_id,
aa.duration,
aa.release_time
FROM
    (SELECT
    a.eid,
    a.duration,
    a.release_time,
    a.time,
    row_number()over(partition by a.eid order by a.time desc) as r2,
    row_number()over(partition by a.eid order by a.time ) as r_2
    FROM
        (SELECT
        u.exam_id as eid,
        TIMESTAMPDIFF(MINUTE,start_time,submit_time) as time,
        duration,
        release_time
        FROM examination_info e right join exam_record u on u.exam_id = e.exam_id
        ) a
    ) aa

WHERE aa.r2=2 or r_2=2
GROUP BY aa.eid,aa.release_time
HAVING max(aa.time)-min(aa.time)>(aa.duration/2)
ORDER BY eid DESC