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

注意：rank()过不了，要用row_number()。
因为rank()表示排名数，会出现并列情况，如两个人并列第二的情况，算第二快/慢用时之差time_diff时，会把并列第二的两个人都算进去。

sum(case when rank_slow=2 then t
                           when rank_fast=2 then -t else 0 end
                          ) as time_diff

全部代码：

select  exam_id,duration,release_time
from (
        select a.exam_id,a.release_time,sum(case when rank_slow=2 then t 
                           when rank_fast=2 then -t else 0 end
                          ) as time_diff,a.duration
        from examination_info a
        left join (
                    select exam_id
                            ,timestampdiff(second,start_time,submit_time) as t
                            ,row_number() over(partition by exam_id 
                             order by timestampdiff(second,start_time,submit_time) desc
                            ) as rank_slow
                            ,row_number() over(partition by exam_id
                            order by timestampdiff(second,start_time,submit_time)
                            ) as rank_fast
                    from exam_record
                    where submit_time is not null
                    ) b 
        on a.exam_id=b.exam_id
        -- where rank_slow=2 and rank_fast=2
        group by a.exam_id
) aa
where time_diff>duration/2*60
order by exam_id desc