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

以防后面想不起来怎么做了。

有个卡点是在最后用例只通过1/2，因为把完成时间为null的剔除了

其实完成时间为null，自然使用时间就会超过duration

还是习惯搞临时表，方便多了，还清晰

正序倒序用row_number然后desc和asc就好

with
    time as (
        select
            er.exam_id,
            ei.duration,
            ei.release_time,
            timestampdiff (minute, er.start_time, er.submit_time) as ti,
row_number() over (
                partition by
                    er.exam_id
                order by
                    timestampdiff (minute, er.start_time, er.submit_time) desc
            ) as rank1,
            row_number() over (
                partition by
                    er.exam_id
                order by
                    timestampdiff (minute, er.start_time, er.submit_time) asc
            ) as rank2
        from
            exam_record er
            left join examination_info ei on er.exam_id = ei.exam_id
    )
select 
    t1.exam_id,t1.duration,t1.release_time
from
    time t1
left join time t2
on t1.exam_id=t2.exam_id
where t1.rank1=2 and t2.rank2=2 and (t1.ti-t2.ti)*2> t1.duration
order by t1.exam_id desc