#自己优化了一下，用秒代替分钟，添加了可能存在并列排名的情况 select distinct exam_id, duration, release_time from (select exam_id as exam_id, duration, release_time, sum(case when rank1 = rank2 then 0 when rank1 = 2 then costtime when rank2 = 2 then -costtime else 0 end) as sub from ( select e_i.exam_id, duration, release_time, timestampdiff(SECOND, start_time, submit_time) as costtime, rank() over(partition by e_r.exam_id order by timestampdiff(SECOND, start_time, submit_time) desc) rank1, rank() over(partition by e_r.exam_id order by timestampdiff(SECOND, start_time, submit_time) asc) rank2 from exam_record e_r join examination_info e_i on e_r.exam_id = e_i.exam_id where submit_time is not null ) table1 group by exam_id ) table2 where sub * 2 >= duration * 60 order by exam_id desc