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

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

https://www.nowcoder.com/practice/b1e2864271c14b63b0df9fc08b559166

select
    exam_id,
    duration,
    release_time
from
(select 
t.exam_id,
duration,
release_time,
row_number() over(partition by t1.exam_id order by t1.difftime desc) as ranking
from examination_info t
inner join (
select 
a.exam_id,
timestampdiff(second,a.start_time,a.submit_time) as difftime
from 
exam_record a
left join examination_info b on a.exam_id = b.exam_id
where a.submit_time is not null
) t1 on t1.exam_id = t.exam_id
where t1.difftime > t.duration*30
) ta
where ta.ranking = 2

union 

select
    exam_id,
    duration,
    release_time
from
(select 
t.exam_id,
duration,
release_time,
row_number() over(partition by t1.exam_id order by t1.difftime asc) as ranking
from examination_info t
inner join (
select 
a.exam_id,
timestampdiff(second,a.start_time,a.submit_time) as difftime
from 
exam_record a
left join examination_info b on a.exam_id = b.exam_id
where a.submit_time is not null
) t1 on t1.exam_id = t.exam_id
where t1.difftime > t.duration*30
order by ranking desc
) tb
where tb.ranking = 2
order by exam_id desc

全部评论

相关推荐

完美的潜伏者许愿简历...:隐藏信息被你提取出来了,暗示,这就是暗示
点赞 评论 收藏
分享
06-26 15:33
青岛工学院 Java
积极的秋田犬要冲国企:他现在邀请我明天面试
点赞 评论 收藏
分享
07-01 23:23
郑州大学 Java
否极泰来来来来:牛客迟早有高三的
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务