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

select answ.exam_id,duration,release_time from 
    (select ans.exam_id,max(ans_time) - min(ans_time) as ans_time_diff from
        (select examination_info.exam_id,timestampdiff(minute,start_time,submit_time) as ans_time,
        row_number() over (partition by examination_info.exam_id order by timestampdiff(minute,start_time,submit_time) desc) as ans_time_rank
        from exam_record join examination_info using(exam_id)
        where submit_time is not null ) ans
    where ans_time_rank != 1 and  ans_time_rank != -1
    group by ans.exam_id) answ
join examination_info using(exam_id)
where ans_time_diff > duration / 2
order by answ.exam_id desc