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

select exam_id,duration,release_time
from (
    select er.exam_id,duration,release_time,submit_time,start_time,
    (row_number() over (partition by er.exam_id order by timestampdiff(minute,submit_time,start_time) desc)) as ranking_slow,
    (row_number() over (partition by er.exam_id order by timestampdiff(minute,submit_time,start_time))) as ranking_fast
    from examination_info as ei,exam_record as er
    where er.exam_id=ei.exam_id and submit_time is not null
) as r
where (case when r.ranking_slow=2 then timestampdiff(minute,submit_time,start_time) else 0 end - 
    case when r.ranking_fast=2 then timestampdiff(minute,submit_time,start_time) else 0 end)>0.5*duration 
order by exam_id desc;