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

# 查询各试卷的答题记录的用时排序及此试卷的id
select ei.exam_id, submit_time-start_time t_d,
rank()over(partition by ei.exam_id order by submit_time-start_time desc) t_rank
from examination_info ei
join exam_record er
on ei.exam_id=er.exam_id
where submit_time is not null
# 查询各试卷的答题记录中第二慢的时间及该试卷的id    （1）
select exam_id, t_d
from(
    select ei.exam_id, submit_time-start_time t_d,
    rank()over(partition by ei.exam_id order by submit_time-start_time desc) t_rank
    from examination_info ei
    join exam_record er
    on ei.exam_id=er.exam_id
    where submit_time is not null
) k1
where t_rank = 2
# 查询各试卷的答题记录中第二慢的时间及该试卷的id    （2）
select exam_id, t_d
from(
    select ei.exam_id, submit_time-start_time t_d,
    rank()over(partition by ei.exam_id order by submit_time-start_time asc) t_rank
    from examination_info ei
    join exam_record er
    on ei.exam_id=er.exam_id
    where submit_time is not null
) k2
where t_rank = 2
--------------------------------------------完整语句如下-------------------------------------------
# 将结果表（1）和（2）以及examination_info连接，并查询第二快和第二慢用时之差大于试卷时长的一半的试卷信息
select ei.exam_id, duration, release_time
from examination_info ei
join (
    select exam_id, t_d t_d1
    from(
        select ei.exam_id, submit_time-start_time t_d,
        rank()over(partition by ei.exam_id order by submit_time-start_time desc) t_rank
        from examination_info ei
        join exam_record er
        on ei.exam_id=er.exam_id
        where submit_time is not null
    ) k1
    where t_rank = 2
) k3
on k3.exam_id=ei.exam_id
join(
    select exam_id, t_d t_d2
    from(
        select ei.exam_id, submit_time-start_time t_d,
        rank()over(partition by ei.exam_id order by submit_time-start_time asc) t_rank
        from examination_info ei
        join exam_record er
        on ei.exam_id=er.exam_id
        where submit_time is not null
    ) k2
    where t_rank = 2
) k4
on k4.exam_id=ei.exam_id
where t_d1-t_d2 > duration/2
order by ei.exam_id desc;