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

select exam_id,duration,release_time
from 
(select a.exam_id,duration,release_time,use_time,
row_number() over(partition by exam_id order by use_time desc) as r1,
row_number() over(partition by exam_id order by use_time ) as r2
from examination_info a 
left join 
(select *,timestampdiff(minute,submit_time,start_time) as use_time
 from exam_record
) b
on a.exam_id=b.exam_id 
)c 
    where r1=2 or r2=2
group by exam_id,duration,release_time
having max(use_time)-min(use_time)>duration*0.5
order by exam_id desc
    ;