MySQL-Consecutive Numbers

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.

题目大意：写一个SQL，查出表Logs中连续出现至少3次的数字。

最简单的方法就是直接使用一套SELECT-FROM-WHERE语句

# Write your MySQL query statement belowSELECT DISTINCT L1.Num ConsecutiveNums FROM Logs L1JOIN Logs L2 ON L1.Id + 1 = L2.IdJOIN Logs L3 ON L1.Id + 2 = L3.IdWHERE L1.Num = L2.Num AND L1.Num = L3.NumORDER BY L1.Num

上面两种方法可以用于找到至少三次连续出现的数字，如果将连续出现的数字扩展到N个，按照上面思路写出的SQL语句就会比较长。因此可以用下面这种方式来查询：

SELECT DISTINCT NumFROM (  SELECT Num,     CASE       WHEN @prev = Num THEN @count := @count + 1      WHEN (@prev := Num) IS NOT NULL THEN @count := 1    END CNT  FROM Logs, (SELECT @prev := NULL) X  ORDER BY Id) AS AWHERE A.CNT >= N