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

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

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

全部评论

相关推荐

不愿透露姓名的神秘牛友
今天 12:04
毕业生招你惹你了,问一个发薪日来一句别看网上乱七八糟的你看哪个工作没有固定发薪日扭头就取消了面试就问了一句公司都是这个态度吗还搞上人身攻击了...
程序员小白条:呃呃呃,都还没面试,我都不会问这么细,何况通不通过,去不去都另说,你没实力和学历的话,在外面就这样,说实话没直接已读不回就不错了,浪费时间基本上
点赞 评论 收藏
分享
nus2201602...:兄弟,你这个简历撕了丢了吧,就是一坨,去找几个项目,理解项目流程,看几遍就是你的了,看看八股就去干了,多看看牛客里别人发出来的简历,对着写,你这写的啥啊,纯一坨
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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