数据库记录

mysql> select * from logs;
+----+-----+
| id | num |
+----+-----+
|  1 | 1   |
|  2 | 1   |
|  3 | 1   |
|  4 | 2   |
|  5 | 1   |
|  6 | 2   |
|  7 | 2   |
+----+-----+

 

查找所有至少连续出现三次的数字

select distinct l1.num as ConsecutiveNums
from logs l1 
join logs l2 on l1.id = l2.id -1 
join logs l3 on l2.id = l3.id - 1
where l1.num = l2.num and l2.num = l3.num;

查询结果

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

 

建表语句

CREATE TABLE `logs` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `num` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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');