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

明确题意：

select 
t6.exam_id,
t6.duration,
t6.release_time
from 
(
    SELECT t3.exam_id,t4.long_time - t3.long_time as diff_long_time  -- 求出差值
    from (
        select * from (
            select * ,
            row_number() over(partition by exam_id order by long_time asc) as row_num -- 用秒 才准确！！
            from (
            select * , 
            TIMESTAMPDIFF(second,start_time,submit_time) as long_time
            from exam_record where submit_time is not null 
            )t1
         )t2 where row_num = 2  
    )t3 -- 第2快
    join (
        select * from (
            select * ,
            row_number() over(partition by exam_id order by long_time desc) as row_num
            from (
            select * , 
            TIMESTAMPDIFF(second,start_time,submit_time) as long_time
            from exam_record where submit_time is not null 
            )t1
        )t2 where row_num = 2  
    )t4 -- 第2慢
    on t3.exam_id = t4.exam_id 
)t5 
join examination_info t6 
on t5.exam_id = t6.exam_id 
where t5.diff_long_time > t6.duration*60 / 2  -- 筛选
order by t6.exam_id desc ;

中间过程：

mysql>  select * from (
    ->         select * ,
    ->         row_number() over(partition by exam_id order by long_time asc) as row_num
    ->         from (
    ->         select * , 
    ->         TIMESTAMPDIFF(second,start_time,submit_time) as long_time
    ->         from exam_record where submit_time is not null 
    ->         )t1
    ->      )t2 where row_num = 2  
    -> ;
+----+------+---------+---------------------+---------------------+-------+-----------+---------+
| id | uid  | exam_id | start_time          | submit_time         | score | long_time | row_num |
+----+------+---------+---------------------+---------------------+-------+-----------+---------+
|  8 | 1006 |    9001 | 2021-09-07 10:01:01 | 2021-09-07 10:12:01 |    84 |       660 |       2 |
|  2 | 1001 |    9002 | 2021-09-01 09:01:01 | 2021-09-01 09:31:00 |    81 |      1799 |       2 |
+----+------+---------+---------------------+---------------------+-------+-----------+---------+
2 rows in set (0.00 sec)

mysql> 
mysql>     select * from (
    ->         select * ,
    ->         row_number() over(partition by exam_id order by long_time desc) as row_num
    ->         from (
    ->         select * , 
    ->         TIMESTAMPDIFF(second,start_time,submit_time) as long_time
    ->         from exam_record where submit_time is not null 
    ->         )t1
    ->     )t2 where row_num = 2  ;
+----+------+---------+---------------------+---------------------+-------+-----------+---------+
| id | uid  | exam_id | start_time          | submit_time         | score | long_time | row_num |
+----+------+---------+---------------------+---------------------+-------+-----------+---------+
|  1 | 1001 |    9001 | 2021-09-01 09:01:01 | 2021-09-01 09:51:01 |    78 |      3000 |       2 |
|  3 | 1002 |    9002 | 2021-09-01 12:01:01 | 2021-09-01 12:31:01 |    81 |      1800 |       2 |
+----+------+---------+---------------------+---------------------+-------+-----------+---------+
2 rows in set (0.00 sec)