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)
