题目要求

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

题目拆分

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

解题步骤

  1. 对 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
  1. 根据表 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 
  1. 根据表 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
  1. 将表 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

知识点

  • 时间相减函数 timestampdiff 函数

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestampdiff

  • 开窗函数运用
  • 表连接

个人感想

此题看起来复杂,但所用的知识点不多,经过拆分,还是比较简单的。