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.

描述

找出连续出现 3 次的数字

代码

-- 方法一

select 
	distinct a.Num as ConsecutiveNums
from 
	Logs a, Logs b, Logs c 
where 
	a.Num = b.Num and a.Num = c.Num and a.Id = b.Id + 1 and a.Id = c.Id + 2

-- 方法二
-- 找出连续出现 n 次的数字

select 
	distinct Num as ConsecutiveNums
from 
	(
	select 
		Num, 
		case 
			when @record = Num then @count := @count + 1
			else @count := 1 
		end n, 
		@record := Num 
	from 
		Logs, 
		(select @record := 0, @count := 0) init
	) tmp
where n >= 3