编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs
表, 1
是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
参考答案:
思路1 开窗
用某条数据的当前1行和后面2,3行,对比 row_1 = row_2 and row_2 = row_3
select distinct Num ConsecutiveNums
from
(
select
Num,
lead(Num,1,null) over(order by id) n2,
lead(Num,2,null) over(order by id) n3
from Logs
)t1
where Num = n2 and Num = n3
思路2 自连接
SELECT distinct a.Num ConsecutiveNums
FROM Logs a,Logs b,Logs c
where a.Num=c.Num and a.Num=b.Num and a.Id=b.Id-1 and b.Id=c.Id-1