题解 | #第二快/慢用时之差大于试卷时长一半的试卷# 居然只有十行代码

with t as (select *,timestampdiff(second,start_time,submit_time) time,row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time)) rank1 
,row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) rank2
from exam_record
where submit_time is not null)
select t1.exam_id,duration,release_time from (
select * from t where rank1=2) t1  join (
select * from t where rank2=2) t2 using(exam_id)
left join examination_info e using(exam_id)
where t2.time-t1.time > e.duration * 0.5 *60
order by exam_id desc