编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
链接:https://leetcode-cn.com/problems/consecutive-numbers
方法一:多表连接
这里的Id连续排列,可用Id进行连接。
连接过程:
select distinct L1.Num ConsecutiveNums
from Logs L1
join Logs L2 on (L1.Num = L2.Num and L1.Id + 1 = L2.Id)
join Logs L3 on (L2.Num = L3.Num and L2.Id + 1 = L3.Id);
方法二: