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

#思路：思路其实简单，分别选出第二快、第二慢的用时，然后跟试卷长度作比较即可。就是代码长。
select tb2.exam_id, duration, release_time
from(

#选出第二慢的数据
select exam_id, a 
from(
select exam_id, timestampdiff(second,start_time,submit_time) a,
dense_rank()over(partition by exam_id order by (timestampdiff(second,start_time,submit_time)) desc) r
from exam_record) tb1
where r=2) tb2
join
#选出第二快的数据
(select exam_id, b
from(
select exam_id, timestampdiff(second,start_time,submit_time) b,
dense_rank()over(partition by exam_id order by (timestampdiff(second,start_time,submit_time))) r
from exam_record) tb3
where r=2) tb4 using(exam_id)

#连接上试卷信息表
join examination_info using(exam_id)
where (a-b)>=duration*30
order by tb2.exam_id desc