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

select 
        exam_id,duration,release_time
from
                    (
                        select a.exam_id,a.duration,a.release_time,b.submit_time,
                        timestampdiff(minute,b.start_time,b.submit_time) as diff,
                        row_number() over (partition by a.exam_id order by timestampdiff(minute,b.start_time,b.submit_time) asc) as ranking1,
                        row_number() over (partition by a.exam_id order by timestampdiff(minute,b.start_time,b.submit_time) desc) as ranking2
                        from examination_info a,exam_record b
                        where a.exam_id=b.exam_id and
                        b.submit_time is not NULL
                    ) t1
    group by exam_id
having SUM(
    case 
        when ranking1=2 then -diff
        when ranking2=2 then diff
        else 0
        end )*2 >=duration
ORDER BY exam_id DESC
;