[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.

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

解题思路

（1）使用三个同样的表a, b, c，a与b的Id相差1，a与c的Id相差2，即可表示连续的Id号；
（2）三个表中的Num值相等。

实现代码

#方法一
select distinct a.Num as ConsecutiveNums
from Logs a
join Logs b on a.Id = b.Id - 1 and a.Num = b.Num
join Logs c on a.Id = c.Id - 2 and a.Num = c.Num;

#方法二
select a.Num as ConsecutiveNums
from Logs a, Logs b,Logs c
where a.Num = b.Num and a.Num = c.Num
and a.Id -1 = b.Id and a.Id - 2= c.Id
group by a.Num;