题解 | #第二快/慢用时之差大于试卷时长一半的试卷#

WITH t as(
SELECT exam_id,
TIMESTAMPDIFF(SECOND,start_time,submit_time) AS use_time,
ROW_NUMBER()OVER(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(MINUTE,start_time,submit_time) ASC) AS fast_rank,
ROW_NUMBER()OVER(PARTITION BY exam_id ORDER BY TIMESTAMPDIFF(MINUTE,start_time,submit_time) DESC) AS slow_rank
FROM exam_record 
WHERE submit_time IS NOT NULL   
),
t1 as(
SELECT t.exam_id,t.use_time,duration,release_time                      
FROM t
JOIN examination_info 
ON t.exam_id =examination_info.exam_id 
WHERE fast_rank =2 
),
t2 as(
SELECT t.exam_id,t.use_time,duration,release_time                      
FROM t
JOIN examination_info 
ON t.exam_id =examination_info.exam_id 
WHERE slow_rank =2 
)
SELECT DISTINCT t1.exam_id,t1.duration,t1.release_time
FROM t1 JOIN t2
WHERE (t2.use_time - t1.use_time)*2 > t1.duration * 60
ORDER BY t1.exam_id DESC;