Consecutive Numbers，consecutivenumbers

Consecutive Numbers，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.    解法一：    參考[Rank Scores](http://blog.csdn.net/havedream_one/article/details/45395063)    從上往下對齊排名，如果相等，則排名相等，不相等則排名加一    代碼如下：    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;