题解 | #第二快/慢用时之差大于试卷时长一半的试卷#
第二快/慢用时之差大于试卷时长一半的试卷
https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
要求：
第二快和第二慢用时之差大于试卷时常一半的试卷信息
拆解条件：
（1）根据试卷id分组： group by exam_id
（2）每个试卷第二快和第二慢用时：
2.1)用时： timestampdiff(minute,start_time,submit_time) m #答题用时
2.2）最快时间排序：按照时间差升序排序 row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)) quick #按照用时从小到大排序（快）
最慢时间排序：按照时间差降序排序 row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)desc) slow #按照用时从大到小排序（慢）
将以上信息作为新表table1
( select exam_id,
timestampdiff(minute,start_time,submit_time) m,
row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)) quick,
row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)desc) slow
from exam_record
) as table1
（3）用时之差：找出正数第二和倒数第二的用时相减
正数第二位：if(quick=2,m,null) a #此处的m表示用时
倒数第二位：if(slow=2,m,null) b
从table1中查询 exam_id,a,b作为表2
select exam_id,if(quick=2,m,null) a,if(slow=2,m,null) b from
(
select exam_id,
timestampdiff(minute,start_time,submit_time) m,
row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)) quick,
row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)desc) slow
from exam_record
) as table1
) as table2
（4）时间差大于试卷时长的一半：
时间差：根据max(b)-max(a)求得，a,b来自table2,试卷时长来自examination_info
将examination_info和table2连接起来
select table2.exam_id from examination_info ei right join
(
select exam_id,if(quick=2,m,null) a,if(slow=2,m,null) b from
(
select exam_id,
timestampdiff(minute,start_time,submit_time) m,
row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)) quick,
row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)desc) slow
from exam_record
) as table1
) as table2 on table2.exam_id=ei.exam_id
group by exam_id
having max(b)-max(a)>0.5*duration
求解：
符合以上要求的试卷信息：
查找出符合以上要求的试卷id，根据id从examination_info表提取试卷信息，则查询语句为见下
排序要求：按照id降序排序：
select exam_id,duration,release_time from examination_info
where exam_id in (
select table2.exam_id from examination_info ei right join
(
select exam_id,if(quick=2,m,null) a,if(slow=2,m,null) b from
(
select exam_id,
timestampdiff(minute,start_time,submit_time) m,
row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)) quick,
row_number()over(partition by exam_id order by timestampdiff(minute,start_time,submit_time)desc) slow
from exam_record
) as table1
) as table2 on table2.exam_id=ei.exam_id
group by exam_id
having max(b)-max(a)>0.5*duration
)
order by exam_id desc;
