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

解题思路分析：
  1. 分别由窗口函数得到 第二快和第二慢的 试卷ID和试卷提交用时
  2. 由两张临时表得到第二快和第二慢的试卷提交时长的时差并join 表 examination_info 判断为时长的一半
  3. 最后得到试卷ID，试卷时长，试卷发布时间，并根据试卷ID降序排列。


select EI.exam_id,
         EI.duration,
         EI.release_time 
    from 
(select exam_id,
       time_Diff 
       from (
   #1 找出每张试卷 第二快 和第二慢的 时间纪录以及时间差 
   select  exam_id ,
           timestampdiff(second,start_time,submit_time) time_Diff, # 时间差 
           row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time)) rank1  # 时长由快变慢 
     from  exam_record 
) A  where rank1 = 2 
) A1
join (
select  exam_id,
        time_Diff 
    from (
   select  exam_id ,
           timestampdiff(second,start_time,submit_time) time_Diff, # 时间差
           row_number() over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) rank2 # 时长由慢变快 
     from  exam_record 
) B where rank2 = 2 
) B1 on A1.exam_id = B1.exam_id 
join examination_info EI on A1.exam_id=EI.exam_id and (B1.time_Diff - A1.time_Diff) > EI.duration*30
order by EI.exam_id desc;