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

select e.exam_id exam_id, duration, release_time
from
(select exam_id, sum((case when m = 2 then t when k = 2 then -t else 0 end)) s
from
(select exam_id, row_number() over (partition by exam_id order by timestampdiff(second, start_time, submit_time)) k,
row_number() over (partition by exam_id order by timestampdiff(second, start_time, submit_time) desc) m,
timestampdiff(second, start_time, submit_time) t
from exam_record
where score is not null) a
group by exam_id) b
left join examination_info e
on b.exam_id = e.exam_id
where 2*s > 60*duration
order by e.exam_id desc;

转化成秒，CASE WHEN计算时间差。