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

-- 笨方法
with 
t1 as
(select er.exam_id,
    ei.duration,
    ei.release_time,
    timestampdiff(second,er.start_time,er.submit_time) as time_diff,
    row_number() over(partition by er.exam_id order by 
        timestampdiff(second,er.start_time,er.submit_time) desc) as rank_desc,
    row_number() over(partition by er.exam_id order by 
        timestampdiff(second,er.start_time,er.submit_time) asc) as rank_asc
from exam_record er
left join examination_info ei
on er.exam_id = ei.exam_id
where er.submit_time is not null)
SELECT s1.exam_id,s1.duration,s1.release_time from 
(select exam_id,time_diff,release_time,duration from t1 where rank_desc=2) s1
inner join 
(select exam_id,time_diff,release_time,duration from t1 where rank_asc=2)  s2
on s1.exam_id = s2.exam_id
where s1.time_diff - s2.time_diff >= s1.duration*60/2
order by s1.exam_id desc;