连续出现的数字的三种解法
题目描述:
编写一个 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