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

问题拆分：

先计算出所有记录的作答用时以及对其进行逆序排序、正序排序，将该子查询提取出来，加个别名，方便后面查询语句使用

找到每张试卷的第2快和第2慢的答题时间

找到（第2慢-第2快）* 2 > 试卷时长的试卷id并排序

1、这里需要用second来计算时间差，用minute会出现误差

区别：

select exam_id, duration,release_time,timestampdiff(second,start_time,submit_time)/60 do_time
from exam_record JOIN examination_info USING(exam_id)
where submit_time is not null
order by exam_id

select exam_id, duration,release_time,timestampdiff(minute,start_time,submit_time) do_time
from exam_record JOIN examination_info USING(exam_id)
where submit_time is not null
order by exam_id

with tmp as (
select 
    exam_id,uid,
    timestampdiff(minute,start_time,submit_time) do_time,
    row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time)/60 desc) ranking_desc, 
    row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time)/60 asc) ranking_asc
from exam_record left join examination_info using(exam_id)
where submit_time is not null
order by exam_id
)

2、找到作答exam_id的第二快和第二慢的答题时间

(select 
    exam_id, do_time min2time
from tmp
where ranking_asc=2
order by exam_id) t1
join 
(select 
    exam_id, do_time max2time
from tmp
where ranking_desc=2
order by exam_id) t2 using(exam_id)

3、找到第二快/慢用时之差大于试卷时长一半的试卷

select exam_id,duration,release_time from 
(select 
    exam_id, do_time min2time
from tmp
where ranking_asc=2
order by exam_id) t1
join 
(select 
    exam_id, do_time max2time
from tmp
where ranking_desc=2
order by exam_id) t2 using(exam_id)
join examination_info using(exam_id)
where (max2time-min2time)*2>=duration
order by exam_id desc