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

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

http://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166

题目要求

“找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序。”

题目拆分

找到第二快用时的试卷
找到第二慢用时的试卷
两者进行连接相减作为条件，从 examination_info 表中删选出结果即可

解题步骤

对 exam_record 表根据耗时时长分别进行逆序排序和顺序排序，记为表t

select
    exam_id,
    timestampdiff(second, start_time, submit_time) as diff_time,
    row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time)) as diff_time_rank_asc,
    row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time) desc) as diff_time_rank_desc
from exam_record
where score is not null

根据表 t，找出耗时第二快的试卷和所用时长，即 exam_id 和 diff_time，记为表 t1

select
    exam_id,
    diff_time as diff_time_asc
from (select
          exam_id,
          timestampdiff(second, start_time, submit_time) as diff_time,
          row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time)) as diff_time_rank_asc,
          row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time) desc) as diff_time_rank_desc
      from exam_record
      where score is not null) as t
where diff_time_rank_asc = 2

根据表 t，找出耗时第二慢的试卷和所用时长，记为表 t2

select
    exam_id,
    diff_time as diff_time_desc
from (select
          exam_id,
          timestampdiff(second, start_time, submit_time) as diff_time,
          row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time)) as diff_time_rank_asc,
          row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time) desc) as diff_time_rank_desc
      from exam_record
      where score is not null) as t
where diff_time_rank_desc = 2

将表 t1 和 t2 连接，获得两表的耗时差值，再连接表 examination_info，找到对应的 exam_id 即可

select
    t1.exam_id
from (select
          exam_id,
          diff_time as diff_time_asc
      from (select
                exam_id,
                timestampdiff(second, start_time, submit_time) as diff_time,
                row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time)) as diff_time_rank_asc,
                row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time) desc) as diff_time_rank_desc
            from exam_record
            where score is not null) as t
      where diff_time_rank_asc = 2 ) as t1 
join (select
          exam_id,
          diff_time as diff_time_desc
      from (select
                exam_id,
                timestampdiff(second, start_time, submit_time) as diff_time,
                row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time)) as diff_time_rank_asc,
                row_number() over(partition by exam_id order by timestampdiff(minute, start_time, submit_time) desc) as diff_time_rank_desc
            from exam_record
            where score is not null) as t
      where diff_time_rank_desc = 2) as t2
on t1.exam_id = t2.exam_id
join examination_info as ei 
on t1.exam_id = ei.exam_id
where t2.diff_time_desc - t1.diff_time_asc > ei.duration / 2
order by exam_id