题解 | #第二快/慢用时之差大于试卷时长一半的试卷#精简代码/解答方法汇总

看了大家的答案，发现基本思路都在以下两种方法之内，暂时没有发现其他太有新意的答案
方法1：找出每份试卷的最短和最长时间，筛选掉后再分组计算第二长和第二短的，这种的优点是比较容易想到，缺点是缺乏泛用性，如果要求的是第三长和第三短很明显这个框架就用不了了

with cte as(
select exam_id,min(timestampdiff(second,start_time,submit_time)) as mintime,max(timestampdiff(second,start_time,submit_time)) as maxtime
from exam_record 
group by exam_id)
select exam_id,duration,releasetime
from(
select e.exam_id,timestampdiff(second,start_time,submit_time) as examtime,duration,i.release_time as releasetime
from exam_record e left join cte c on e.exam_id=c.exam_id
left join examination_info i on e.exam_id=i.exam_id
where timestampdiff(second,start_time,submit_time)<>mintime and timestampdiff(second,start_time,submit_time)<>maxtime
order by exam_id) a
group by exam_id
having max(examtime)-min(examtime)>duration/2
order by exam_id desc;

方法2：建立通用表表达式的时候把每份试卷的所有答题时间遍历，然后分别按照所用时间正序排列一次，所用时间倒序排列一次，之后在聚合筛选时只需要where筛选出想要的位次就可以了，这种方法优点时具有泛用性，缺点就是一般直接想不到，个人认为是本题的最优解。

with cte as(
select id,uid,exam_id,timestampdiff(second,start_time,submit_time) as examtime,
row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) as time_rank_desc,
row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) ) as time_rank_asc
 from exam_record) 
 select c.exam_id,duration,release_time
 from cte c left join examination_info i on c.exam_id=i.exam_id
 where time_rank_desc=2 or time_rank_asc=2
 group by exam_Id
 having max(examtime)-min(examtime)> i.duration*60/2
 order by exam_id desc;