连续出现的数字的三种解法

题目描述：

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+—-+—–+
| Id | Num |
+—-+—–+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+—-+—–+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+—————–+
| ConsecutiveNums |
+—————–+
| 1 |
+—————–+

三种解法第一种：3表查询之无脑查询

select distinct a1.num ConsecutiveNums
from Logs a1 inner join Logs a2 on a2.Id-a1.Id=1
inner join Logs a3 on a3.Id-a1.Id=2
where a1.Num=a2.Num and a1.Num = a3.Num

三种解法第二种:2表查询HAVING COUNT

select distinct a1.Num as ConsecutiveNums
from Logs as a1
join Logs as a2
on a1.Num = a2.Num
and a1.Id - a2.Id between 0 and 2
group by a1.Id,a1.Num
having count(*)=3

三种解法第三种：窗口函数统计差值

select t3.Num as ConsecutiveNums
from(
    select t2.Num,count(*) as maincount
    from(
        select t1.id,t1.num,
        row_number () over (order by Id) - row_number () over (partition by Num order by Id) as mainrank
        from Logs as t1
    ) as t2
    group by t2.num,t2.mainrank 
) as t3
where t3.maincount>=3