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

with a as
(select * from(
    select exam_id,
row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) desc) as rk,
timestampdiff(minute,start_time,submit_time) t
from exam_record ) top2
where rk=2

union 

select * from(
    select exam_id,
row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) ) as rk,
timestampdiff(minute,start_time,submit_time) t
from exam_record ) top2
where rk=2)

select exam_id,duration,release_time
from(
select a.exam_id,max(t)-min(t) as cha,duration,release_time 
from a 
join examination_info ei on a.exam_id=ei.exam_id
group by a.exam_id)b
where cha>=0.5*duration
order by exam_id desc

先用CTE将每个exam的第二快和第二慢的时差取出，放在一起

再通过一个主查询实现条件第二快和第二慢用时之差大于等于试卷时长的一半的试卷信息，按试卷ID降序排序