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

笔记，便于查阅与复盘。

我的解题思路

第一步，拆解任务。

1. 第二快与第二慢的用时，即要将每一份试卷所有有效作答的用时计算出来，排序，选出第二快与第二慢。
2. 要与试卷的duration进行比较，因此第一步结果表与examination_info进行连接，比较用时与duration/2的大小，选出用时更大的试卷。

第二步，实现任务。

1. 第二快与第二慢的用时，即要将每一份试卷所有有效作答的用时计算出来，排序，选出第二快与第二慢。

对于第一个任务的实现，有几个关键步骤：

• 计算用时

select submit_time-start_time spend_time,
分组排序
from exam_record
where submit_time is not null

• 排序。排序分为用时没有重复和用时有重复的情况，这两种情况下的第二快和第二慢怎么选？目前只想到第一步，就是按照时间差正序排和逆序排（这里不要考虑最大最小值，不需要），到这里就不知道要怎么挑选出第二快和第二慢并计算二者的差了。

select submit_time-start_time spend_time,
row_number()over(partition by exam_id order by submit_time-start_time esc) rank_esc,
row_number()over(partition by exam_id order by submit_time-start_time desc) rank_desc
from exam_record
where submit_time is not null

题解方案——

• 挑选第二快和第二慢。因为row_number()是无论重复都唯一排名，因此排序后的值为2就是第二快和第二慢的。我们首先将正序排和逆序排的字段命名为rank_esc，rank_desc，然后选出二者的值为2的记录。

select *
from(
  select submit_time-start_time spend_time,
  row_number()over(partition by exam_id order by submit_time-start_time esc) rank_esc,
  row_number()over(partition by exam_id order by submit_time-start_time desc) rank_desc
  from exam_record
  where submit_time is not null)
 where rank_esc=2 or rank_desc=2

• 接下来要计算二者之差，并且是对每一份试卷都计算，从题解得到的方案是通过sum()求和，这里可以将用时较小的值赋值为负，然后按照exam_id分组直接求和即可。同时与另一张表进行连接，选出需要的字段。(到这里就会发现需要groupby，mysql99版本之前的版本都不支持groupby后的select里出现非聚合字段，这种情况就会很麻烦，这个不合适的思维一直没有改过来）

select *,
	sum(case when(rank_esc)=2 then spend_time = -spend_time else spend_time end) sum_time,
from(
  select submit_time-start_time spend_time,
  row_number()over(partition by exam_id order by submit_time-start_time esc) rank_esc,
  row_number()over(partition by exam_id order by submit_time-start_time desc) rank_desc
  from exam_record
  where submit_time is not null) t1
 where rank_esc=2 or rank_desc=2
 group by exam_id
 left join examination_info
 using(exam_id)

• 最后选出需要的字段。

select exam_id, duration, release_time
from
  (select *,
	  sum(case when(rank_esc)=2 then spend_time = -spend_time else spend_time end) sum_time,
  from(
	select submit_time-start_time spend_time,
	row_number()over(partition by exam_id order by submit_time-start_time esc) rank_esc,
	row_number()over(partition by exam_id order by submit_time-start_time desc) rank_desc
	from exam_record
	where submit_time is not null) t1
   where rank_esc=2 or rank_desc=2
   group by exam_id
   left join examination_info
   using(exam_id)
  ) t2
  where sum_time>duration/2

出错。

核心问题主要包括：

1. groupby和连接的顺序问题。在这种嵌套查询比较多的情况下，对每一层查询，涉及到多表时，考虑连接和groupby先后顺序，每执行一个查询就问自己，可不可以做连接？可不可以做分组？
2. 对groupby的条件限制规则/where的条件限制规则/join的条件限制规则的使用场景、顺序不够明晰，写着写着就很混乱；
3. 对于聚合函数和case when，if的结合使用不太熟练。可以记住常用场景：对于同一组别内部进行对比或求和的操作使用groupby结合聚合函数、对于多判断或对某字段进行划分分层使用case when，单判断用if。