Consecutive Numbers


  • 0

    Click here to see the full article post


  • 0
    Z

    SELECT *
    FROM
    Logs l1,
    Logs l2,
    Logs l3
    WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num


  • 0
    X

    @zhouyanghhu I just tried your solution for the data below. it returns duplicated Nums. (1 1 1.). As the editorial solution suggested, 'distinct' is needed.

    +----+-----+
    | Id | Num |
    +----+-----+
    | 1 | 1 |
    | 2 | 1 |
    | 3 | 1 |
    | 4 | 1 |
    | 5 | 1 |
    | 6 | 2 |
    | 7 | 2 |
    +----+-----+


  • 0
    S

    why?
    SELECT s.last_num AS consecutivenums FROM (
    SELECT id,IF(@last_num = t.num,@rn:=@rn+1,@rn:=1) col,
    (@last_num :=t.num) last_num
    FROM LOGS t,(SELECT @last_num :=0,@rn :=1) b) s WHERE s.col>=3;


  • 0
    Z

    That is 3 times.Not at least 3 times.So the answer doesn't perfect?


  • 0
    Z

    I got it.The answer is correct and I was wrong before.How stupy I am!


  • 0
    K

    Slightly different approach to the outlined answer:

    SELECT DISTINCT L1.Num AS ConsecutiveNums
    FROM Logs L1
    LEFT JOIN Logs L2 ON L2.Id = (L1.Id + 1)
    LEFT JOIN Logs L3 ON L3.Id = (L1.Id + 2)
    WHERE L1.Num = L2.Num
    AND L2.Num = L3.Num


  • 0
    B

    Another approach using a nested query. I think it is better
    set @prev = null;
    set @conCount = 0;

    select distinct t.Num as ConsecutiveNums from
    (select Num,
    case
    when @prev = Num then @conCount := @conCount + 1
    when (@prev := Num) is not null then @conCount := 1
    end as ConCount
    from Logs
    ) as t
    where t.ConCount = 3


  • 0
    L

    This approach is wrong when there are more than 3 consecutive number showed up


  • 0
    L

    Sorry I was wrong


  • 0
    J

    select a.num as ConsecutiveNums
    from (
    select count(num) as count1, num
    from Logs
    group by num
    ) a
    where a.count1 > 3
    I got the correct result, but cant submit it ...who can tell me there is any problem with my code???thanks very much


  • 0
    Z

    @JoyJoe The description require the number that appears consecutively for at least three times,not just count it. So you have to solve the problem of consecutiveness.


  • 0
    J

    @Zhenguo0 OK I know, thanks very much. I will try it again !!!


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.