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

题目主要信息：

• 找到第二快和第二慢用时之差大于试卷时长的一半的试卷信息，按试卷ID降序排序
• 试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）
• 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）

问题拆分：

• 找到每份试卷的ID、限制时间、发布时间以及每份试卷被完成的耗时、耗时降序排名和增序排名。这里的不用去重，也不用分组，因为每一份都要计算耗时：
  • 试卷信息与做题信息分布在两个表中，因此要将两个表以exam_id连接。知识点：join...on...
  • 只查询有提交时间的时间，筛掉没做完的试卷。知识点：where
  • 试卷ID、限制时间、发布时间直接获取，完成的耗时使用timestampdiff函数根据开始时间和提交时间计算分钟数差值。知识点：timestampdiff()
  • 利用分组聚合排名对每一种试卷的完成耗时分别进行增序排名和降序排名。知识点：row_number() over partition by
  • 查询出的表格记为table1
• 根据上面筛选出来的信息查询每份试卷的限制时间、发布时间及第二快与第二慢的差值：
  • 每张不同的试卷都会有一个数据，因此以试卷ID作为分组。知识点：group by
  • 每组试卷的ID、限制时间、发布时间都可以由table1直接查询到。
  • 将每组试卷的完成时间累加，只有当最慢排名为2时才加正值，最快排名为2时加负值，其余情况加0.这样刚好是用时第二多减去用时第二少。 sum(case when rank1 = 2 then costtime when rank2 = 2 then -costtime else 0 end) as sub 知识点：sum()、case when...then...when...then...else...end
  • 查询出的结果记为table2
• 最后从table2出筛选出大于等于限制时间一半的试卷ID，限制时间和发布时间
• 输出按照试卷ID的降序排列。知识点：order by

代码：

select distinct exam_id, duration, release_time
from
    (select exam_id as exam_id, duration, release_time,
           sum(case when rank1 = 2 then costtime when rank2 = 2 then -costtime else 0 end) as sub
    from (
        select e_i.exam_id, duration, release_time,
        timestampdiff(minute, start_time, submit_time) as costtime,
        row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) desc) rank1,
        row_number() over(partition by e_r.exam_id order by timestampdiff(minute, start_time, submit_time) asc) rank2
        from exam_record e_r join examination_info e_i
        on e_r.exam_id = e_i.exam_id
        where submit_time is not null 
    ) table1
    group by exam_id
) table2
where sub * 2 >= duration
order by exam_id desc