题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
http://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
升序和降序各给数据算一次排序。 自连接，使用两种顺序为2的做题时间相减 ID降序。
with a as((
select e1.exam_id,e1.release_time,
timestampdiff(minute,start_time,submit_time) diff,
dense_rank()over(partition by exam_id order by timestampdiff(second,start_time,submit_time)desc) diff1,
dense_rank()over(partition by exam_id order by timestampdiff(second,start_time,submit_time)asc) diff2,
duration from examination_info e1
left join exam_record e2
on e1.exam_id=e2.exam_id ))
select distinct a1.exam_id,a1.duration,a1.release_time from a a1
left join a a2
on a1.exam_id =a2.exam_id
and a1.diff1=2
and a2.diff2=2
where a1.diff-a2.diff >a1.duration/2
order by exam_id desc
查看17道真题和解析
曼迪匹艾公司福利 94人发布