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

select exam_id, examination_info.duration, examination_info.release_time from
(select exam_id,short_time from (select exam_id, timestampdiff(second,start_time,submit_time) as short_time, row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) asc) as ranking from exam_record) as rank_table1
where ranking = 2) as short_time_table
join
(select exam_id,rank_table2.long_time from (select exam_id, timestampdiff(second,start_time,submit_time) as long_time, row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) as ranking from exam_record) as rank_table2
where ranking = 2) as long_time_table
using(exam_id)
left join examination_info using (exam_id)
where (long_time_table.long_time - short_time_table.short_time) > examination_info.duration/2 * 60
order by exam_id desc