题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
http://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
注意：相差时长大于等于限定时长的一半
with t as
(select t.exam_id,
TIMESTAMPDIFF(MINUTE,start_time,submit_time) as time_gap,
row_number()over(partition by exam_id order by TIMESTAMPDIFF(MINUTE,start_time,submit_time) desc) as rn1,
row_number()over(partition by exam_id order by TIMESTAMPDIFF(MINUTE,start_time,submit_time)) as rn2
FROM examination_info t
join exam_record t1 on t.exam_id = t1.exam_id
where submit_time is not null
)
select t1.exam_id,duration,release_time
from examination_info t1
join
(select exam_id,sum(if(rn1=2,time_gap,0))-sum(if(rn2=2,time_gap,0)) as total_time_gap
from t
group by exam_id) t2
on t1.exam_id = t2.exam_id
where total_time_gap>=duration/2
order by exam_id desc
