MySQL实现连续出现的数字

最新推荐文章于 2024-08-02 10:48:08 发布

Jaemon

最新推荐文章于 2024-08-02 10:48:08 发布

阅读量419

点赞数

分类专栏： MySQL 算法

本文链接：https://blog.csdn.net/u010979642/article/details/115693564

版权

MySQL 同时被 2 个专栏收录

30 篇文章 1 订阅

订阅专栏

算法

29 篇文章 2 订阅

订阅专栏

数据库记录

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