题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
终于算是自己做出一道比较麻烦的题，这题其实不算特别难，主要就是理清逻辑，无限套娃，在这里分享一下自己的思路，虽然代码比较长，但绝对好理解。
第1步：先用from子查询+降序排序窗口函数，制作包含不同试卷ID及其对应被作答用时第二慢的所花的时间的表a
(select exam_id,desc_time,desc_ranking from
(select exam_id,timestampdiff(second,start_time,submit_time) desc_time,row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) desc_ranking from exam_record
where submit_time is not null) t1
where desc_ranking = 2) a
（注意要限制submit_time is not null，不然就会有null值进入所花时间的排序！！！）
第2步：用跟第一步同样的方法，将降序改为升序，制作包含不同试卷ID及其对应被作答用时第二快的所花的时间的表b
(select exam_id,asc_time,asc_ranking from
(select exam_id,timestampdiff(second,start_time,submit_time) asc_time,row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) asc) asc_ranking from exam_record
where submit_time is not null) t2
where asc_ranking = 2) b
第3步：将表a的exam_id和表b的exam_id作为连接键，将表a和表b连接起来，制作包含不同试卷ID及其对应第二快和第二慢用时之差的表c
(select a.exam_id exam_id,desc_time - asc_time time from
(select exam_id,desc_time,desc_ranking from
(select exam_id,timestampdiff(second,start_time,submit_time) desc_time,row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) desc_ranking from exam_record
where submit_time is not null) t1
where desc_ranking = 2) a
join
(select exam_id,asc_time,asc_ranking from
(select exam_id,timestampdiff(second,start_time,submit_time) asc_time,row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) asc) asc_ranking from exam_record
where submit_time is not null) t2
where asc_ranking = 2) b
on a.exam_id = b.exam_id) c
最后一步：将表c的exam_id和原表examination_info的exam_id作为连接键，将表c和表examination_info连接起来，添加限制条件后得到我们最终的运行代码
select c.exam_id,duration,release_time from
(select a.exam_id exam_id,desc_time - asc_time time from
(select exam_id,desc_time,desc_ranking from
(select exam_id,timestampdiff(second,start_time,submit_time) desc_time,row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) desc_ranking from exam_record
where submit_time is not null) t1
where desc_ranking = 2) a
join
(select exam_id,asc_time,asc_ranking from
(select exam_id,timestampdiff(second,start_time,submit_time) asc_time,row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) asc) asc_ranking from exam_record
where submit_time is not null) t2
where asc_ranking = 2) b
on a.exam_id = b.exam_id) c
left join examination_info on c.exam_id = examination_info.exam_id
where time > duration*60/2
order by c.exam_id desc
投递BOSS直聘等公司7个岗位