题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
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