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

select distinct exam_id,duration,release_time
from (select 
    t1.exam_id,
    t2.duration,
    t2.release_time,
    timestampdiff(minute,start_time,submit_time) as costtime,
    row_number() over(partition by t1.exam_id order by timestampdiff(minute,start_time,submit_time) asc) as rank1,
    row_number() over(partition by t1.exam_id order by timestampdiff(minute,start_time,submit_time) desc) as rank2
    from exam_record t1
    left join examination_info t2
    using(exam_id)
    where t1.submit_time is not null) t
group by exam_id
having sum(case when rank1=2 then -costtime when rank2=2 then costtime else 0 end)*2 >= duration
order by exam_id desc