LeetCode数据库—连续出现的数字

SQL架构

Create table If Not Exists Logs (Id int, Num int);
Truncate table Logs;
insert into Logs (Id, Num) values ('1', '1');
insert into Logs (Id, Num) values ('2', '1');
insert into Logs (Id, Num) values ('3', '1');
insert into Logs (Id, Num) values ('4', '2');
insert into Logs (Id, Num) values ('5', '1');
insert into Logs (Id, Num) values ('6', '2');
insert into Logs (Id, Num) values ('7', '2');

查看记录

mysql> select * from logs;
+------+------+
| Id   | Num  |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    4 |    2 |
|    5 |    1 |
|    6 |    2 |
|    7 |    2 |
+------+------+
7 rows in set (0.00 sec)

要求：编写一个 SQL 查询，查找所有至少连续出现三次的数字。
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

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

解法一：
连接时只对id做出限制，后面添加where，限制num取值

mysql> select 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=l3.num;
+-----------------+
| ConsecutiveNums |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

解法二:
先基于id和num进行连接，然后直接分组即可

mysql> select 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
    -> group by l1.num;
+-----------------+
| ConsecutiveNums |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)