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

# 创建临时表，表中包含所需列外，再分别添加考试时长tdiff，考试时长升序r1和考试时长倒序r2
with temp as(
select 
    exam_id,
    duration,
    release_time,
    timestampdiff(minute, start_time, submit_time) tdiff,
    row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time)) r1,
    row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time) desc) r2
from examination_info ei left join exam_record er using(exam_id)
)

# 选出所要列，并满足第二慢和第二快之差sub大于duration的一半，并按exam_id降序排列
select 
    b.exam_id exam_id,
    b.duration duration,
    b.release_time release_time
from(
    # 将升序第二和降序第二连成一条数据，计算出差值sub
    select 
        t1.*, (t2.tdiff - t1.tdiff) sub
    from temp t1 left join temp t2 on t1.exam_id = t2.exam_id
    where t1.r1 = 2 and t2.r2 = 2
) b 
where b.sub * 2 > b.duration
order by b.exam_id desc