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

select 
    exam_id,
    duration,
    release_time
from 
(
    select
    er.exam_id,
    ei.duration,
    release_time,
    timestampdiff(second,start_time,submit_time) as exam_time,
    row_number() over(partition by ei.exam_id order by timestampdiff(second,start_time,submit_time)) r1,
    row_number() over(partition by ei.exam_id order by timestampdiff(second,start_time,submit_time) desc) r2
    from exam_record er
    inner join examination_info ei
    on er.exam_id = ei.exam_id
    where er.score is not null
) a
group by exam_id
having (max(if(r2=2,exam_time,0)) - max(if(r1=2,exam_time,0))) >= duration/2
思路是这样的，但是不知道max还能这样用，瞬间好像知道mysql的逻辑了
order by exam_id desc