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

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

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


全部评论

相关推荐

点赞 评论 收藏
分享
tttk_:就是人多。 有的是条件和你差不多然后没在od待过的人。 所以就拿这个筛你了。 就和卡学历一样,人太多了。 从公司角度,这样做节省精力,更方便。 没办法谁叫现在人多呢
第一份工作能做外包吗?
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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