【LeetCode】数据库 - 连续出现的数字

【题目描述】

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

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

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

【题目解答】

首先建表、生成数据

create table `logs` (
	`id` int (11) not null auto_increment,
	`num` int (11) not null,
	primary key (`id`)
) engine = innodb charset = utf8;

insert into logs(num) value(1),(1),(1),(2),(1),(2),(2);

这种题可以用拼接表的方式来实现，使用join将同样的3张表拼成一份，如下：

mysql> select * from logs l1 join logs l2 on l1.id = l2.id join logs l3 on l1.id
 = l3.id;
+----+-----+----+-----+----+-----+
| id | num | id | num | id | num |
+----+-----+----+-----+----+-----+
|  1 |   1 |  1 |   1 |  1 |   1 |
|  2 |   1 |  2 |   1 |  2 |   1 |
|  3 |   1 |  3 |   1 |  3 |   1 |
|  4 |   2 |  4 |   2 |  4 |   2 |
|  5 |   1 |  5 |   1 |  5 |   1 |
|  6 |   2 |  6 |   2 |  6 |   2 |
|  7 |   2 |  7 |   2 |  7 |   2 |
+----+-----+----+-----+----+-----+
7 rows in set (0.02 sec)

题目要求至少连续出现三次，所以我们可以通过on后面的id值来控制连续，让l1.id = l2.id - 1；l1.id = l3.id - 2，如下：

mysql> select * from logs l1 join logs l2 on l1.id = l2.id - 1 join logs l3 on l
1.id = l3.id - 2;
+----+-----+----+-----+----+-----+
| id | num | id | num | id | num |
+----+-----+----+-----+----+-----+
|  1 |   1 |  2 |   1 |  3 |   1 |
|  2 |   1 |  3 |   1 |  4 |   2 |
|  3 |   1 |  4 |   2 |  5 |   1 |
|  4 |   2 |  5 |   1 |  6 |   2 |
|  5 |   1 |  6 |   2 |  7 |   2 |
+----+-----+----+-----+----+-----+
5 rows in set (0.00 sec)

所以，我们可以在这么一张拼接完的表上找到三列num都相等的一条记录，还要考虑1连续出现6次的情况，还是1，所以去重，最终如下：

mysql> select distinct(l1.num) as consecutivenums from logs l1 join logs l2 on l
1.id = l2.id - 1 join logs l3 on l1.id = l3.id - 2 where l1.num = l2.num and l2.
num = l3.num;
+-----------------+
| consecutivenums |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)