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

select
    exam_id,
    duration,
    release_time
from
(select 
t.exam_id,
duration,
release_time,
row_number() over(partition by t1.exam_id order by t1.difftime desc) as ranking
from examination_info t
inner join (
select 
a.exam_id,
timestampdiff(second,a.start_time,a.submit_time) as difftime
from 
exam_record a
left join examination_info b on a.exam_id = b.exam_id
where a.submit_time is not null
) t1 on t1.exam_id = t.exam_id
where t1.difftime > t.duration*30
) ta
where ta.ranking = 2

union 

select
    exam_id,
    duration,
    release_time
from
(select 
t.exam_id,
duration,
release_time,
row_number() over(partition by t1.exam_id order by t1.difftime asc) as ranking
from examination_info t
inner join (
select 
a.exam_id,
timestampdiff(second,a.start_time,a.submit_time) as difftime
from 
exam_record a
left join examination_info b on a.exam_id = b.exam_id
where a.submit_time is not null
) t1 on t1.exam_id = t.exam_id
where t1.difftime > t.duration*30
order by ranking desc
) tb
where tb.ranking = 2
order by exam_id desc