leetcode MYSQL数据库题目180

180.Consecutive Numbers

１、题目与答案

Write a SQL query to find all numbers that appear at least three times consecutively.

编写SQL查询以查找连续出现至少三次的所有数字
Table:Logs:

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

For example, given the aboveLogs table, 1 is the only number that appears consecutively for at least three times.

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

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

答案：
select distinct log1.num as ConsecutiveNums
from Logs log1, Logs log2, Logs log3
where log3.id=log2.id+1 and log2.id=log1.id+1 and log1.num = log2.num and log2.num = log3.num;

---

２、知识点总结

验证步骤：
create table logs(
id int NULL,
num int NULL
);
insert into logs
values(1,1),(2,1),(3,1),(4,2),(5,1),(6,2),(7,2);

知识点：

• select ... as作为列名
• where条件筛选