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

select distinct exam_id,duration,release_time
from(
select exam_id, duration, release_time,sum(case when rank1 = 2 then dur when rank2 = 2 then -dur else 0 end) as sub 
from(
select t1.exam_id, t2.duration, t2.release_time,rank1,rank2,dur
from
(select uid, exam_id,timestampdiff(minute, start_time, submit_time) dur,
row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time) desc) rank1, row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time)asc) rank2
from exam_record
where submit_time is not null) t1
left join examination_info t2
on t1.exam_id = t2.exam_id) t3
group by exam_id
having sub * 2 >= duration) t4
order by exam_id desc

逐步查询