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

select
    exam_id,
    duration,
    release_time
from
(select
    exam_id,
    sum(case when rank1 = 2 then do_time else -do_time end) as sub,
    duration,
    release_time
from
(
select
    er.exam_id,
    timestampdiff(minute, start_time, submit_time) as do_time,
    row_number() over (partition by er.exam_id 
                        order by timestampdiff(minute, start_time, submit_time) desc) 
    as rank1,
    row_number() over (partition by er.exam_id 
                        order by timestampdiff(minute, start_time, submit_time) asc) 
    as rank2,
    duration,
    release_time
from exam_record as er
inner join examination_info
using(exam_id)
where submit_time is not null
) as t1
where  rank1 = 2 or rank2 = 2
group by exam_id,release_time,duration) as t2
where sub*2 >= duration
order by exam_id desc

主要是分成三个部分， 首先：通过窗口函数row_number()得到做题时间的升序和降序的序号。 第二步：通过rank1和rank2（也就是升序和降序的标号）来选出第二快和第二慢的。通过group by对不同得试卷进行分组。通过case when语句将第二快的分钟数变成负数，第二慢的不变，进行聚合（相加sum）。得到题目要求的分钟数。 第三步：从上一步的查询中得到符合要求的分钟数。 要点：row_number的使用。可以多个一起使用，不影响。