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

第一步：先把所有记录的时间差及其大小顺序计算出来 第二步：找到每个exam第二快kuai_2和第二慢man_2的时间差 第三步：将第二步找到的kuai_2-man_2计算出来，找到大于0.5的duration的数据

select e.exam_id,e.duration,release_time
from 
(
    select t.exam_id
    ,max(case when r1=2 then time_diff end) as kuai_2
    ,max(case when r2=2 then time_diff end) as man_2
    from 
    (
        SELECT *
        ,TIMESTAMPDIFF(MINUTE,submit_time,start_time) as time_diff
        ,dense_rank() 
        over(partition by exam_id order by TIMESTAMPDIFF(MINUTE,submit_time,start_time) desc) as r1
        ,dense_rank() 
        over(partition by exam_id order by TIMESTAMPDIFF(MINUTE,submit_time,start_time)) as r2
        from exam_record
        where submit_time is not null
    ) t 
    group by t.exam_id
) t 
join examination_info e 
on e.exam_id=t.exam_id
where (kuai_2-man_2)>=0.5*duration
order by t.exam_id desc;