# 2022-11-25：连续出现的数字。编写一个 SQL 查询，查找所有至少连续出现三次的数字

2022-11-25：连续出现的数字。编写一个 SQL 查询，查找所有至少连续出现三次的数字。

``````DROP TABLE IF EXISTS logs;
CREATE TABLE logs (
id int(11) NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO logs VALUES ('1', '1');
INSERT INTO logs VALUES ('2', '1');
INSERT INTO logs VALUES ('3', '1');
INSERT INTO logs VALUES ('4', '2');
INSERT INTO logs VALUES ('5', '1');
INSERT INTO logs VALUES ('6', '2');
INSERT INTO logs VALUES ('7', '2');
``````

sql语句如下：

``````

``````
``````SELECT DISTINCT
l1.num AS ConsecutiveNums
FROM
logs l1,
logs l2,
logs l3
WHERE
l1.id = l2.id - 1
AND l2.id = l3.id - 1
AND l1.num = l2.num
AND l2.num = l3.num``````

1周前 评论

``````    select
distinct Num as ConsecutiveNums
from
(select Num,
if(@pre=Num,@count := @count+1,@count := 1) as nums,
@pre:=Num
from logs as l ,
(select @pre:= null,@count:=1) as pc
) as n
where nums >=3;``````

``````    select
Num,
max(nums) as ConsecutiveNums
from
(select Num,
if(@pre=Num,@count := @count+1,@count := 1) as nums,
@pre:=Num
from logs as l ,
(select @pre:= null,@count:=1) as pc
) as n
GROUP BY Num``````
1周前 评论

169

10

16

5