问题：

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.

+-----------------+| ConsecutiveNums |+-----------------+| 1               |+-----------------+

解决：

① 找出连续出现3次以上的数的值。由于需要找三次相同数字，所以我们需要建立三个表的实例，我们可以用l1分别和l2, l3内交，l1和l2的Id下一个位置比，l1和l3的下两个位置比，然后将Num都相同的数字返回即可。1822 ms

SELECT DISTINCT l1.Num ConsecutiveNums FROM Logs l1

JOIN Logs l2 ON l1.Id = l2.Id - 1JOIN Logs l3 ON l1.Id = l3.Id - 2WHERE l1.Num = l2.Num AND l2.Num = l3.Num;

② 直接在三个表的实例中查找，然后把四个条件限定上，就可以返回正确结果了。 2803 ms

SELECT DISTINCT l1.Num ConsecutiveNums FROM Logs l1,Logs l2,Logs l3

WHERE l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1AND l1.Num = l2.Num AND l2.Num = l3.Num;

③ 用到了变量count和pre，分别初始化为0和-1。2438 ms

SELECT DISTINCT Num ConsecutiveNums FROM (

    SELECT Num, := IF( = Num, + 1,1) As n, := Num     FROM Logs,(SELECT := 0,@pre := -1) As init     ) As t WHERE t.n >= 3;