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

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

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

with cte_get_duration as (
    select exam_id,
    timestampdiff(minute,start_time,submit_time) as time_spand
    from exam_record
    where submit_time is not null
)
# 获取做题时长
select exam_id,
duration,
release_time
from (
    SELECT exam_id,duration,release_time,
    sum(case when rank_desc=2 then time_spand when rank_asc=2 then -time_spand else 0 end) as sub
    # 只能用CASE WHEN 不能用两个IF语句获取分支数据
    from (
        select exam_id,duration,release_time,time_spand,
        # 加上做题时长一列,以便后续与名次一同进行处理获得时间差
        row_number() over(partition by exam_id order by time_spand desc) as rank_desc,
        row_number() over(partition by exam_id order by time_spand asc) as rank_asc
        # 新增两列数据:时间差降序/升序排名
        from examination_info
        join exam_record using(exam_id)
        join cte_get_duration using(exam_id)
    ) as t1
    group by exam_id
    # 分组,对分组试卷类型时长数据进行计算
) as t2
where sub * 2 >= duration
order by exam_id desc


全部评论

相关推荐

不愿透露姓名的神秘牛友
07-24 12:26
点赞 评论 收藏
分享
牛客83700679...:简历抄别人的,然后再投,有反馈就是简历不行,没反馈就是学历不行,多投多改只要技术不差机会总会有的
点赞 评论 收藏
分享
zzzzhz:兄弟你先猛猛投简历至少三百家,能约到面试就去面。最近可以速成智能小车,智慧家居烂大街的项目,不需要自己写,只需要把里面的代码讲解看明白就行。把其中涉及到的八股文都拿出来单独背一下,我去年找工作就一个智能小车智慧家居找了10k差不多。
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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