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

方法一： 参照学习讨论区的

select distinct c.exam_id,duration,release_time from 

    (select a.exam_id, 
    # 窗口函数 nth_value(measure_expr,n) OVER (partition by   order by)	返回窗口框架第 n 行的 measure_expr 值
            nth_value(TIMESTAMPDIFF(minute,start_time,submit_time),2) over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) desc ) as low_2,
            nth_value(TIMESTAMPDIFF(minute,start_time,submit_time),2) over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) asc) as fast_2,
            duration,release_time
    from exam_record a left join examination_info b on a.exam_id = b.exam_id
    ) c  # 此处筛选表中 除过None 所有的相同 exam_id对应的数据都相同

where low_2-fast_2>duration*0.5
order by exam_id desc;

方法二： 自己写的

# SELECT exam_id, duration,release_time
# FROM
# (
#     SELECT exam_id, duration,d_time,release_time
#     FROM 
#         (
#         SELECT er.exam_id, duration,release_time
#                ,TimeStampDiff(SECOND, start_time, submit_time)/60 as d_time
#                ,RANK() over(partition BY er.exam_id ORDER BY TimeStampDiff(SECOND, start_time, submit_time)/60 )  as rk_f
#                ,RANK() over(partition BY er.exam_id ORDER BY TimeStampDiff(SECOND, start_time, submit_time)/60 DESC)  as rk_L
#         FROM exam_record er LEFT JOIN 
#              examination_info ei on er.exam_id=ei.exam_id
#         ) a # 此时主要作用时进行排序
#      where rk_f =2 OR rk_L = 2 # 由于不在同一列，因此共同满足是错误的，应该使用OR
# ) b # 此时表中只有 试卷的信息和 对应两个时间（最快+最慢）；当时想的时不论顺序用ABS()就能找到时间差，请继续。。。
# GROUP BY exam_id
# having MAX(d_time)-MIN(d_time) > duration*0.5 
# # 书接上文，分组之后怎么取出这两个数让人有点麻烦，直到在讨论区找到 MIN+MAX ；还有另一个点： 对于分组后的筛选应该用HAVING
# ORDER BY exam_id DESC