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

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

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

select c.exam_id, duration, release_time
from 

(   select exam_id,time1
    from
    (select exam_id, timestampdiff(minute,start_time,submit_time)time1,
ROW_NUMBER() OVER (
         PARTITION BY exam_id
         ORDER BY timestampdiff(minute,start_time,submit_time) DESC
       ) AS rank_de   
from exam_record)a1
where rank_de=2 
)a

left join 

(select exam_id,time2
from(
    select exam_id, timestampdiff(minute,start_time,submit_time)as time2,
ROW_NUMBER() OVER (
         PARTITION BY exam_id
         ORDER BY timestampdiff(minute,start_time,submit_time)
       ) AS rank_asc      
from exam_record
)a2
where rank_asc=2
)b
on a.exam_id=b.exam_id


left join 
(
    select exam_id, tag, duration, release_time
    from examination_info
)c
on b.exam_id=c.exam_id
where (time1-time2)*2>=duration
# and rank_de=2 and  rank_asc=2
order by exam_id desc

全部评论

相关推荐

不愿透露姓名的神秘牛友
07-08 14:10
点赞 评论 收藏
分享
05-27 14:57
西北大学 golang
强大的社畜在走神:27届真不用急,可以搞点项目、竞赛再沉淀沉淀,我大二的时候还在天天打游戏呢
投递华为等公司10个岗位
点赞 评论 收藏
分享
06-07 19:59
门头沟学院 C++
补药卡我啊😭:都快15年前的了还在11新特性
你的简历改到第几版了
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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