题目要求

“找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷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

2. 根据表 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 

3. 根据表 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

4. 将表 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

• 开窗函数运用
• 表连接

个人感想

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