题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
思路：
给完成时间（submit_time）和（start_time）的 差 排序，然后只保留正序为 2 或者 倒序为 2 的信息
使用函数：
求时间差 timestampdiff(second,start_time,submit_time)
排序 row_number()
1.首先给原来的exam_record表加入时间差的正序和倒序 以及 时间差 ,同时排除完成时间为空（Null）的信息，partiton by exam_id根据考试编号聚合
select *,timestampdiff(second,start_time,submit_time) tm, row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) rk1, row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) ) rk2 from exam_record where submit_time is not null2.链接考试信息表，并保留序号为2的所有信息
select exam_id,duration,release_time from (select *,timestampdiff(second,start_time,submit_time) tm, row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) rk1, row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) ) rk2 from exam_record where submit_time is not null ) t join examination_info using(exam_id) where rk1 = 2 or rk2 = 23.因为第二步只有序号为2的信息，所以用考试编号（exam_id）聚合，然后用大的时间减去小的时间就好了（PS：因为之前计算时间差单位是用的秒，所以duration要乘以60）
group by exam_id having max(tm)-min(tm) > duration * 0.5 *60 order by exam_id desc
最后是完整代码
select exam_id,duration,release_time from (select *,timestampdiff(second,start_time,submit_time) tm, row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) rk1, row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) ) rk2 from exam_record where submit_time is not null ) t join examination_info using(exam_id) where rk1 = 2 or rk2 = 2 group by exam_id having max(tm)-min(tm) > duration * 0.5 *60 order by exam_id desc
查看26道真题和解析
