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

select t1.exam_id,duration,release_time
from (
select temp.exam_id,time_, row_number()over(partition by temp.exam_id order by time_ ) asc_ranking,
      row_number()over(partition by temp.exam_id order by time_ desc) as desc_ranking
    from (select uid,exam_id,timestampdiff(minute,start_time,submit_time) as time_
          from exam_record
    ) temp
) t1
left join examination_info t2
on t1.exam_id=t2.exam_id
where asc_ranking=2 or desc_ranking=2
group by t1.exam_id
having max(time_)-min(time_)>duration/2
order by t1.exam_id desc