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

select exam_id,duration,release_time from 
    (select exam_id,duration,release_time,t from
        (
        select exam_id,duration,release_time,timestampdiff(second,start_time,submit_time) as t,
            row_number()over(partition by er.exam_id order by 
                             timestampdiff(second,start_time,submit_time)
                             desc) as r1,
            row_number()over(partition by er.exam_id order by 
                             timestampdiff(second,start_time,submit_time)
                             ) as r2
            from exam_record as er join examination_info as ei using(exam_id)
            where timestampdiff(second,start_time,submit_time) is not null
        ) as a
    where r1=2 or r2=2
    ) as b
group by exam_id
having (max(t)-min(t)>=(duration*30))
order by exam_id desc
#子查询嵌套
#先对时间差进行正序和倒序排序，在选择分别为2的表示第二快和第二慢，这样每个试卷都可以
#得到两行数据，之后再用最大值减最小值大于时间的一般即可（这里用秒）