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

select distinct a.exam_id,b.duration,b.release_time from(
select exam_id,
    timestampdiff(minute,start_time,submit_time) as t,
dense_rank() over(partition by exam_id order by 
timestampdiff(minute,start_time,submit_time) asc) t_a,
dense_rank() over(partition by exam_id order by 
timestampdiff(minute,start_time,submit_time) desc) t_d
from exam_record where submit_time is not null )a,examination_info b 
where a.exam_id=b.exam_id
and (a.t_d=2 or a.t_a=2)
group by 1,2,3
having max(a.t)-min(a.t)>=avg(b.duration)/2
order by a.exam_id desc