#4、最后，将经过条件筛选后的字段，挑选出展示字段进行展示＆排序#
select t5.exam_id,t5.duration,t5.release_time
from

#3、再次，筛选出完成时间最慢前2，以及完成时间最快前2#
(select *
from

#2、其次，按照刚才计算好的完成时间字段，从大到小＆从小到大进行排序（2个全新的排序字段）#
(select *,
row_number() over(partition by t3.exam_id order by done_time desc) as max_rank,row_number() over(partition by t3.exam_id order by done_time) as min_rank
from

#1、首先，计算出各用户完成各试卷的时间，同时将未完成的筛选掉#
(select t2.exam_id,duration,release_time,
timestampdiff(minute,start_time,submit_time) as done_time
from exam_record as t1
left join examination_info as t2 
on t1.exam_id = t2.exam_id
where score is not null) 
#1#

as t3) 
#2#

as t4
where t4.max_rank = 2
or t4.min_rank = 2)
#3#

as t5
where (case when t5.max_rank = 2 then t5.done_time else 0 end) - (case when t5.min_rank = 2 then t5.done_time else 0 end) > t5.duration*0.5
order by exam_id desc
#4、完成最慢第2-完成最快第2 > 试卷规定市场的1/2#;