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

select exam_id,
duration,
release_time
 from (
select
    exam_id,
    duration,
    release_time,
    timestampdiff(minute,start_time,submit_time) as continueTime,
    row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) desc) as ordertimestamp1,
     row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) asc) as ordertimestamp2
    from examination_info
    join exam_record using (exam_id)
where submit_time is not null
) as newform
where newform.ordertimestamp1 = 2 or newform.ordertimestamp2 = 2
group by 
exam_id
having sum(if(ordertimestamp1=2, continueTime,if(ordertimestamp2 = 2,-continueTime,0)))  >= 0.5*newform.duration
order by 
exam_id desc

先通过row_number()over(partition by exam_id，order by timestampdiff(minute,start_time,submit_time))//找到时间间隔降序的例子，然后通过聚合函数sum(if(ordertimestamp1=2, continueTime,if(ordertimestamp2 = 2,-continueTime,0))) >= 0.5*newform.duration，取出符合条件的数据即可。