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

# 找出每张试卷答题用时的排名
with t as (
    select *, row_number() over(partition by exam_id order by timestampdiff(second, start_time, submit_time) desc) slow_rank,
    row_number() over(partition by exam_id order by timestampdiff(second, start_time, submit_time)) quick_rank,
    timestampdiff(second, start_time, submit_time) time_use
from exam_record
)

select distinct er.exam_id, duration, release_time
from examination_info ei join exam_record er
on ei.exam_id = er.exam_id
join (
    # 找到第二快和第二慢用时之差
    select t1.exam_id, t1.time_use-t2.time_use time_sub
    from t t1, t t2
    where t1.exam_id = t2.exam_id
        and t1.slow_rank = 2
        and t2.quick_rank = 2
) t3
on ei.exam_id = t3.exam_id
where 30*duration < t3.time_sub
order by er.exam_id desc;

select distinct exam_id, duration,release_time
from (
select ei.exam_id,
    nth_value(timestampdiff(second,start_time,submit_time),2) over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) as low_2,
    nth_value(timestampdiff(second,start_time,submit_time),2) over(partition by exam_id order by timestampdiff(second,start_time,submit_time) ) as fast_2,
    duration,release_time
from examination_info ei join exam_record er
on ei.exam_id = er.exam_id) t
where 30*duration < low_2-fast_2
order by exam_id desc;