美文网首页
2020-11-11-Mysql(领扣-连续出现的数字)

2020-11-11-Mysql(领扣-连续出现的数字)

作者: 冰菓_ | 来源:发表于2020-11-22 07:40 被阅读0次
    image.png

    1.如果序号连续的情况下解答

    select
    Log1.Num AS  ConsecutiveNums
    from
    Logs as Log1,Logs as Log2,Logs as Log3
    where Log1.Num = Log2.Num  AND Log2.Num = Log3.Num and Log1.ID = Log2.ID -1 AND Log2.ID = Log3.ID-1
    

    2.我的解答?未通过(与其他答案)

    考虑ID是不连续的且是不重复的.采用 number() 窗口
    如果ID字段与ID2字段相减得到的结果是一致的,我们可以确认它们是连续的
    select
    t3.Num
    from
         (
    select
    distinct t2.Num,
    COUNT(1)  AS result
    from
    (
    select
    t1.ID,
    t1.Num,
    (t1.ID - t1.ID2) as Result
    from
    (
    select
    ID,
    Num,
    row_number() over(partition by Num  order by ID) as ID2
    from Logs) t1 ) t2
    group by  t2.Num,t2.Result ) t3
    where t3.result >=3;
    
    select distinct Num ConsecutiveNums
    from (select dif,num,count(*)
        from (select id,num,row_number() over(order by id)-row_number() over(partition by num order by id) dif from Logs) a
        group by dif,num having count(*)>=3) b;
    

    相关文章

      网友评论

          本文标题:2020-11-11-Mysql(领扣-连续出现的数字)

          本文链接:https://www.haomeiwen.com/subject/soyebktx.html