题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
http://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
1.row_number
select exam_id, duration, release_time from ( SELECT er.exam_id, uid, duration, release_time, TIMESTAMPDIFF(MINUTE, start_time, submit_time) as spend_time, ROW_NUMBER() over( partition by exam_id order by TIMESTAMPDIFF(MINUTE, start_time, submit_time) desc ) as rank1, ROW_NUMBER() over( partition by exam_id order by TIMESTAMPDIFF(MINUTE, start_time, submit_time) asc ) as rank2 from exam_record as er left join examination_info as ei on er.exam_id = ei.exam_id where submit_time is not null ) as a group by exam_id having sum( case when rank1 = 2 then spend_time when rank2 = 2 then - spend_time else 0 end ) * 2 >= duration order by exam_id desc
2.nth_value
SELECT distinct exam_id, duration, release_time FROM ( SELECT er.exam_id, uid, duration, release_time, TIMESTAMPDIFF(MINUTE, start_time, submit_time) as spend_time, nth_value(TIMESTAMPDIFF(MINUTE, start_time, submit_time), 2) over( PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) DESC ) as max2_time_took, nth_value(TIMESTAMPDIFF(MINUTE, start_time, submit_time), 2) over( PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(MINUTE, start_time, submit_time) asc ) as min2_time_took FROM exam_record as er left JOIN examination_info ei on er.exam_id = ei.exam_id WHERE submit_time IS NOT NULL ) as a WHERE max2_time_took - min2_time_took >= duration / 2 ORDER BY exam_id DESC
投递完美世界等公司10个岗位 >
