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

with cte_get_duration as (
    select exam_id,
    timestampdiff(minute,start_time,submit_time) as time_spand
    from exam_record
    where submit_time is not null
)
# 获取做题时长
select exam_id,
duration,
release_time
from (
    SELECT exam_id,duration,release_time,
    sum(case when rank_desc=2 then time_spand when rank_asc=2 then -time_spand else 0 end) as sub
    # 只能用CASE WHEN 不能用两个IF语句获取分支数据
    from (
        select exam_id,duration,release_time,time_spand,
        # 加上做题时长一列，以便后续与名次一同进行处理获得时间差
        row_number() over(partition by exam_id order by time_spand desc) as rank_desc,
        row_number() over(partition by exam_id order by time_spand asc) as rank_asc
        # 新增两列数据：时间差降序/升序排名
        from examination_info
        join exam_record using(exam_id)
        join cte_get_duration using(exam_id)
    ) as t1
    group by exam_id
    # 分组,对分组试卷类型时长数据进行计算
) as t2
where sub * 2 >= duration
order by exam_id desc