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

结构化思维：①先用窗口函数分别选出第二快、第二慢的行以及相应的答题时间；②将两表连接后再与e_i表连接，用以对比duration。代码如下：

select rk1.exam_id, duration, release_time from

# 第一步：选出各个试卷答题时间第二快的行，用到row_number排序
(select exam_id, a from
(select exam_id, timestampdiff(second,start_time,submit_time) a, 
row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time)) r1 
from exam_record where submit_time is not null) as tb1
where r1=2) as rk1

# 第三步：第二快、第二慢的行进行连接
left join

# 第二步：选出各个试卷答题时间第二慢的行，用到row_number排序
(select exam_id, a from
(select exam_id, timestampdiff(second,start_time,submit_time) a, 
row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) r2
from exam_record where submit_time is not null) as tb2
where r2=2) as rk2
using(exam_id)

# 第四步：再与examination_info连接，用来对比duration
left join examination_info using(exam_id)
where rk2.a-rk1.a>duration*30
order by rk1.exam_id desc