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

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

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

全部评论

相关推荐

不愿透露姓名的神秘牛友
06-04 15:20
牛客61197583...:看到室友一个个没怎么学通过关系直接入职或者接到面试,真的很难受。八股不知道背了多少遍,hot100也刷了1.5遍了,但就是没有面试的机会,唉
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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