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

笨办法，一环套一环 datediff（sql server 用 ） timestampdiff（mysql用）

select a.exam_id,duration,release_time  from 
(select e1.exam_id,(e1.dc-e2.dc)as d from (select exam_id,timestampdiff(second ,start_time,submit_time) as dc ,rank()over(partition by exam_id order by (timestampdiff(second ,start_time,submit_time)) desc) as rk1
from exam_record ) e1
join (select exam_id,timestampdiff(second ,start_time,submit_time) as dc ,rank()over(partition by exam_id order by (timestampdiff(second ,start_time,submit_time)) asc) as rk2
from exam_record ) e2 
on e1.exam_id=e2.exam_id
where rk1=2 and rk2=2
group by e1.exam_id,(e1.dc-e2.dc)) a
left join examination_info b
on a.exam_id=b.exam_id
where a.d>b.duration*30
order by a.exam_id desc