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

select c.exam_id, duration, release_time
from 

(   select exam_id,time1
    from
    (select exam_id, timestampdiff(minute,start_time,submit_time)time1,
ROW_NUMBER() OVER (
         PARTITION BY exam_id
         ORDER BY timestampdiff(minute,start_time,submit_time) DESC
       ) AS rank_de   
from exam_record)a1
where rank_de=2 
)a

left join 

(select exam_id,time2
from(
    select exam_id, timestampdiff(minute,start_time,submit_time)as time2,
ROW_NUMBER() OVER (
         PARTITION BY exam_id
         ORDER BY timestampdiff(minute,start_time,submit_time)
       ) AS rank_asc      
from exam_record
)a2
where rank_asc=2
)b
on a.exam_id=b.exam_id


left join 
(
    select exam_id, tag, duration, release_time
    from examination_info
)c
on b.exam_id=c.exam_id
where (time1-time2)*2>=duration
# and rank_de=2 and  rank_asc=2
order by exam_id desc