ConsecutiveNumbers

ConsecutiveNumbers

    Write a SQL query to find all numbers that appear at least three times consecutively.

    +----+-----+
    | Id | Num |
    +----+-----+
    | 1  |  1  |
    | 2  |  1  |
    | 3  |  1  |
    | 4  |  2  |
    | 5  |  1  |
    | 6  |  2  |
    | 7  |  2  |
    +----+-----+
    For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.
    解法一：

    從上往下對齊排名，如果相等，則排名相等，不相等則排名加一
    代碼如下：
    select num,@curRank := @curRank+IF(@preNum = num,0,1) as rank ,@preNum:=num from logs s,(select @curRank := 0) r,(select @preNum := null) p;
    +------+------+--------------+
    | num  | rank | @preNum:=num |
    +------+------+--------------+
    |    1 |    1 |            1 |
    |    1 |    1 |            1 |
    |    1 |    1 |            1 |
    |    2 |    2 |            2 |
    |    1 |    3 |            1 |
    |    2 |    4 |            2 |
    |    2 |    4 |            2 |
    +------+------+--------------+
    如上所示，如果一個num的連續排名超過3則符合題意。
    select num,count(rank) as count from (
        select num,@curRank := @curRank+IF(@preNum = num,0,1) as rank ,@preNum:=num from logs s,(select @curRank := 0) r,(select @preNum := null) p ) t 
        group by rank;
    +------+-------+
    | num  | count |
    +------+-------+
    |    1 |     3 |
    |    2 |     1 |
    |    1 |     1 |
    |    2 |     2 |
    +------+-------+
    得到上述的表之後，再使用having條件選擇。
    最終結果：
    select distinct num from(
        select num,count(rank) as count from (
            select num,@curRank := @curRank+IF(@preNum = num,0,1) as rank ,@preNum:=num from logs s,(select @curRank := 0) r,(select @preNum := null) p ) t 
            group by rank having count >= 3;
        ) tmp;
    解法二：
    1、從第一條記錄搜索，前後相同，count++，不相等，count=1;
    2、判斷，若rank大於3則符合題意
    select distinct num 
        from ( 
            select num,@curRank := @preRank+IF(@preNum = num,1,0),@preRank :=IF(@preNum = num,@curRank,1) as rank,@preNum := num 
                from Logs s,(select @preRank := 1) r,(select @preNum := null) p
        ) t 
        where rank >= 3;