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

select exam_id,duration,release_time from (
    select  
    exam_id,sum(case when rank_asc = 2 then time_1 else 0 end) as asc_d
    ,sum(case when rank_desc = 2 then time_1 else 0 end) as desc_d
    from (
        select exam_id,timestampdiff(second,start_time,submit_time) as time_1,
        row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time)) as rank_asc,
        row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) as rank_desc
        from exam_record t 
        where submit_time is not null ) t1
    group by exam_id)t2 
        join examination_info t3 using(exam_id)
where (desc_d-asc_d)>(duration*30)
order by exam_id desc

这个思路就是先使用窗口函数算出排名，然后取出第二和倒数第二的值，然后计算即可。

这个方法就是对写作格式要求比较高，容易在小地方写错，导致报错，需要细心一点。

同时学到了新的窗口函数

select distinct exam_id,duration,release_time from(
select exam_id,duration,release_time,
nth_value(TIMESTAMPDIFF(second,start_time,submit_time),2) 
    over(partition by exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) desc ) as desc_d,
nth_value(TIMESTAMPDIFF(second,start_time,submit_time),2) 
    over(partition by exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time)  ) as asc_d
from exam_record  t join  examination_info t1 using(exam_id))t2
where desc_d-asc_d>(duration*30)
order by exam_id desc

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html