题解 | #第二快/慢用时之差大于试卷时长一半的试卷#精简代码/解答方法汇总
第二快/慢用时之差大于试卷时长一半的试卷
http://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166
看了大家的答案,发现基本思路都在以下两种方法之内,暂时没有发现其他太有新意的答案
方法1:找出每份试卷的最短和最长时间,筛选掉后再分组计算第二长和第二短的,这种的优点是比较容易想到,缺点是缺乏泛用性,如果要求的是第三长和第三短很明显这个框架就用不了了
with cte as(
select exam_id,min(timestampdiff(second,start_time,submit_time)) as mintime,max(timestampdiff(second,start_time,submit_time)) as maxtime
from exam_record
group by exam_id)
select exam_id,duration,releasetime
from(
select e.exam_id,timestampdiff(second,start_time,submit_time) as examtime,duration,i.release_time as releasetime
from exam_record e left join cte c on e.exam_id=c.exam_id
left join examination_info i on e.exam_id=i.exam_id
where timestampdiff(second,start_time,submit_time)<>mintime and timestampdiff(second,start_time,submit_time)<>maxtime
order by exam_id) a
group by exam_id
having max(examtime)-min(examtime)>duration/2
order by exam_id desc;
方法2:建立通用表表达式的时候把每份试卷的所有答题时间遍历,然后分别按照所用时间正序排列一次,所用时间倒序排列一次,之后在聚合筛选时只需要where筛选出想要的位次就可以了,这种方法优点时具有泛用性,缺点就是一般直接想不到,个人认为是本题的最优解。
with cte as(
select id,uid,exam_id,timestampdiff(second,start_time,submit_time) as examtime,
row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) desc) as time_rank_desc,
row_number()over(partition by exam_id order by timestampdiff(second,start_time,submit_time) ) as time_rank_asc
from exam_record)
select c.exam_id,duration,release_time
from cte c left join examination_info i on c.exam_id=i.exam_id
where time_rank_desc=2 or time_rank_asc=2
group by exam_Id
having max(examtime)-min(examtime)> i.duration*60/2
order by exam_id desc;