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

1.先将每张试卷的答题记录按照用时排序，挑出第二快和第二慢 降序row_number()over()=2 和 升序row_number()over() =2
2.比较用时长度 sum(if(rank_1 = 2, use_time,0))-sum(if(rank_2=2,use_time, 0))

select exam_id, duration, release_time from(
    select exam_id, duration, release_time, sum(if(rank_1 = 2, use_time,0))-sum(if(rank_2=2,use_time, 0)) as sub from(
        select exam_record.exam_id, duration,score, timestampdiff(minute,start_time,submit_time) as use_time, release_time,
        row_number() over(partition by examination_info.exam_id order by timestampdiff(minute,start_time,submit_time) desc) as rank_1,
        row_number() over(partition by examination_info.exam_id order by timestampdiff(minute,start_time,submit_time)) as rank_2
        from examination_info, exam_record
        where exam_record.exam_id = examination_info.exam_id and submit_time is not null) as inf
    group by exam_id) inf2
where 2*sub >= duration
order by exam_id desc

不知道为啥明明说的是大于duration一半的，但是第二个用例把等于一半的也算上了