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

我喜欢用中间表

with 
#两张表合并命名为表t，并增加时间差字段cha
t as 
(select a.tag,a.release_time,a.duration,b.* ,
TIMESTAMPDIFF(second, b.start_time, b.submit_time)  as cha
from
exam_record as b
left join examination_info as a
using(exam_id) ),

#将exam_id、第二快、duration,cha 结果命名为表t1
t1 as
(select exam_id, `第二快`, duration,cha from 
(select
exam_id,
row_number() OVER (PARTITION BY exam_id ORDER BY cha desc )  as `第二快`,
duration,
cha
from t) as t1
where `第二快`=2),

#将exam_id、第二慢结果命名为表t2
t2 as
(select exam_id, `第二慢`,cha  from 
(select
exam_id,
row_number() OVER (PARTITION BY exam_id ORDER BY cha asc )  as `第二慢`,
cha
from t) as t1
where `第二慢`=2)

#将表t1和t2合并,筛选，排序得到最终结果
select exam_id,	duration,	release_time from examination_info
where exam_id in 
(select exam_id from t1 
left join t2
using(exam_id)
where (t1.cha - t2.cha) > t1.duration*30)
order by exam_id desc