题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
http://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
-- 排名倒数第二
select t2.* from
(
select t1.,
row_number() over(ORDER BY t1.sc asc) pm1
from
(
select ,TIMESTAMPDIFF(MINUTE,start_time,submit_time) as sc
from exam_record
WHERE submit_time is not NULL
) as t1
) t2 WHERE t2.pm1=2
JOIN
-- 第二名
select t4. from
(
select t3.,
row_number() over(ORDER BY t3.sc desc) pm2
from
(
select *,TIMESTAMPDIFF(MINUTE,start_time,submit_time) as sc
from exam_record
WHERE submit_time is not NULL
) as t3
) t4 WHERE t4.pm2=2
ON t2.exam_id = t4.exam_id
WHERE (t4.sc - t2.sc) >
-- 最终代码
select ei.exam_id,ei.duration,ei.release_time from
(
select t2.* from
(
select t1.*,
row_number() over(ORDER BY t1.sc asc) pm1
from
(
select *,TIMESTAMPDIFF(MINUTE,start_time,submit_time) as sc
from exam_record
WHERE submit_time is not NULL
) as t1
) t2 WHERE t2.pm1=2
) t5
JOIN
(
select t4.* from
(
select t3.*,
row_number() over(ORDER BY t3.sc desc) pm2
from
(
select ,TIMESTAMPDIFF(SECOND,start_time,submit_time) as sc
from exam_record
WHERE submit_time is not NULL
) as t3
) t4 WHERE t4.pm2=2
) t6
ON t5.exam_id = t6.exam_id
LEFT JOIN
examination_info as ei ON t5.exam_id = ei.exam_id and t6.exam_id = ei.exam_id
WHERE (t6.sc - t5.sc) > ei.duration60/2 ORDER BY ei.exam_id DESC
