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

先上完整代码，本人笨比一个，所以写的很麻烦，在这里再理一下思路。

select
     e.exam_id,
     duration,
     release_time
from examination_info e
join (
    select *
    from (select
              exam_record. exam_id,
              timestampdiff(second,start_time,submit_time) uset,
              row_number()  over (
                  partition BY exam_record.exam_id
                  order BY timestampdiff(second,start_time,submit_time)
                  ) u_t
          from exam_record
          where submit_time is not null
        ) u_t_k2
    where u_t = 2
)u_t_k21
on  u_t_k21.exam_id = e.exam_id
join (
    select *
    from (select
              exam_record.exam_id,
              timestampdiff(second,start_time,submit_time) uset,
              row_number()  over (
                  partition BY exam_record.exam_id
                  order BY timestampdiff(second,start_time,submit_time ) desc
                  ) u_t
          from exam_record) u_t_m2
    where u_t = 2
)u_t_m21
on  u_t_k21.exam_id = u_t_m21.exam_id
where u_t_m21.uset - u_t_k21.uset > e.duration * 30
order by 
    e.exam_id desc   

这个题最难的地方在自己要有一个足够清晰的思路，要在哪里写需求的东西，来分解一下这个题吧。

首先是第二快，所以就窗口函数排个序先，

select
    exam_id,
    row_number()  over (
    partition BY exam_id
    order BY (timestampdiff(second,start_time,submit_time))
    ) u_t
from exam_record;

然后发现排名不对劲所以就

timestampdiff(second,start_time,submit_time),

看了一眼，原来null也给排上了！没有时间直接第一，所以就加了一个where

select
          exam_id,
          timestampdiff(second,start_time,submit_time) uset,
          row_number()  over (
              partition BY exam_id
              order BY timestampdiff(second,start_time,submit_time)
              ) u_t
      from exam_record
      where submit_time is not null

看一下，哎好了，所以就准备取出第二就再裹一层加个where u_t = 2

那这不就清晰了？再整一个desc不就是倒数第二

select *
    from (select
              exam_record.exam_id,
              timestampdiff(second,start_time,submit_time) uset,
              row_number()  over (
                  partition BY exam_record.exam_id
                  order BY timestampdiff(second,start_time,submit_time ) desc
                  ) u_t
          from exam_record) u_t_m2
    where u_t = 2

好完事这俩结果表一连，发现最后要的居然在第三张表上，没事join连就完了。

然后时间一半，行，筛，选呗

where u_t_m21.uset - u_t_k21.uset > e.duration * 30

哎好，样例过了能交了？

不行，题没看全呢，我这样一整exam_id顺序还是升序的，只不过样例答案刚好就是第一个且只有一个，过不了实际的题例， 所以这在加一个order by

order by 
    e.exam_id desc 

OK 完事！