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

select 
    c.exam_id,
    duration,
    release_time
from(  
    select
        b.exam_id,
        max(case when rka=2 then td end) as quick,
        max(case when rkde=2 then td end) as slow #注意，case when rkde=2 then td end返回的是一组数（所有试卷类型的第二名），但是group by exam-id后，就只返回一个数，因此外面用max(),min(),avg()返回的结果都一样
    from(
        #首先建立有关试卷做题时间信息表
        select
            a.exam_id,
            timestampdiff(minute,start_time,submit_time) as td,
            row_number() over (partition by a.exam_id order by timestampdiff(minute,start_time,submit_time) desc) as rkde,
            row_number() over (partition by a.exam_id order by timestampdiff(minute,start_time,submit_time) asc) as rka
        from 
            exam_record as a
        where submit_time is not null
    )as b
    group by b.exam_id
)as c
inner join examination_info as d
on c.exam_id = d.exam_id
where slow-quick >= duration/2 #一定要加=，题目没说清，否则提交不通过
order by exam_id desc;