题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
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
