本SQL问题是 #180. Consecutive Numbers
问题
编写SQL，找出至少连续出现三次的所有数字
Tips:
借助一个游标变量
@cu来记录重复次数
select distinct(p.Num) as 'ConsecutiveNums' from (
select l.Id,l.Num,
case
when @pre = l.Num THEN @cur := @cur + 1
when @pre := l.Num THEN @cur := 1
else 1
END as c
from Logs as l , (select @pre :=0 ,@cur := 1)t)p
WHERE p.c = 3
方法2
通过错位连接来筛选(通用性不高)
SELECT DISTINCT L1.Num AS ConsecutiveNums
FROM Logs L1
INNER JOIN Logs L2
ON L1.Id=L2.Id-1
INNER JOIN Logs L3
ON L2.Id=L3.Id-1
WHERE L1.Num=L2.Num AND L1.Num=L3.Num;
网友评论