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

select 
exam_id,
duration,
release_time
from
(
select 
*,
lag(diff) over(partition by exam_id order by diff desc) as flag
from 
(
select
t2.exam_id,
duration,
release_time,
timestampdiff(minute,start_time,submit_time) as diff,
row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) desc) as fast,
row_number() over (partition by exam_id order by timestampdiff(minute,start_time,submit_time) asc ) as slow
from 
exam_record t1  join examination_info t2 on t1.exam_id=t2.exam_id
)tmp
where fast=2 or slow=2
)tmp2
where flag-diff>duration/2
order by exam_id desc

tmp 表：得到试卷信息和快，慢两种排序

tmp2表：将快，慢用时加入到一行中

最终表：筛选得到快-慢>试卷时间一半到试卷信息