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

#timestampdiff + 窗口函数row_number() 正序+倒序排列 + where + group by + max -min
#数据连接
SELECT
    ei.exam_id
    ,ei.duration
    ,ei.release_time
from examination_info ei join
    (#筛选每个用户正序和倒序第二的时间（where）并利用groupby 计算差值
    select
        tmp2.exam_id
        ,max(tmp2.dt) - min(tmp2.dt) as diff
    from(#按照试卷完成时间进行排序
        select
            tmp1.*
            ,row_number() over(partition by tmp1.exam_id order by tmp1.dt) as rk
            ,row_number() over(partition by tmp1.exam_id order by tmp1.dt desc) as drk
        from (#先准备试卷完成时间 timestampdiff
            select
                *
                ,timestampdiff(second,start_time,submit_time)/60 as dt
            from exam_record
            where submit_time is not null
            )tmp1
        )tmp2
    where tmp2.rk=2 or tmp2.drk=2
    group by tmp2.exam_id
    )tmp3
on ei.exam_id=tmp3.exam_id and tmp3.diff>(ei.duration/2)
order by ei.exam_id desc