题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
http://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
问题拆解
- 求出每个人做题的时间差，并精确到秒：timestampdiff(second, start_time, submit_time)
- 按照卷子的类型将完成时间排序，并找到第二名和倒数第二名，因此可以正序和倒序排两次：row_number() over(partition by er.exam_id order by timestampdiff(second, start_time, submit_time) desc) as ranking1， 正序的为ranking2；之后筛选ranking1或者ranking2找到对应的行
- 表格的联结：from exam_record as er join examination_info as ei on er.exam_id = ei.exam_id
- 按照卷子的id分组求最大值和最小值的差（因为我们之前已经通过ranking1和ranking2字段筛选出两行，因此max()-min()直接可以求出时间差）： max(exam_time) - min(exam_time) > duration * 0.5 * 60
代码如下
select exam_id, duration, release_time from( select er.exam_id, duration, release_time, timestampdiff(second, start_time, submit_time) as exam_time, row_number() over(partition by er.exam_id order by timestampdiff(second, start_time, submit_time) desc) as ranking1, row_number() over(partition by er.exam_id order by timestampdiff(second, start_time, submit_time)) as ranking2 from exam_record as er join examination_info as ei on er.exam_id = ei.exam_id ) as temp1 where ranking1 = 2&nbs***bsp;ranking2 = 2 group by exam_id , duration, release_time having max(exam_time) - min(exam_time) > duration * 0.5 * 60 order by exam_id desc