#思路:思路其实简单,分别选出第二快、第二慢的用时,然后跟试卷长度作比较即可。就是代码长。 select tb2.exam_id, duration, release_time from( #选出第二慢的数据 select exam_id, a from( select exam_id, timestampdiff(second,start_time,submit_time) a, dense_rank()over(partition by exam_id order by (timestampdiff(second,start_time,submit_time)) desc) r from exam_record) tb1 where r=2) tb2 join #选出第二快的数据 (select exam_id, b from( select exam_id, timestampdiff(second,start_time,submit_time) b, dense_rank()over(partition by exam_id order by (timestampdiff(second,start_time,submit_time))) r from exam_record) tb3 where r=2) tb4 using(exam_id) #连接上试卷信息表 join examination_info using(exam_id) where (a-b)>=duration*30 order by tb2.exam_id desc