一.连续出现的数字
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
解法一:自连接三次,筛选l1,l2,l3的id连续且Num相等的行
注:如果数字连续出现超过三次,Num就会出现重复值,所以需要使用distinct去重
该方法实现简单,但是时间复杂度较大,且如果改成连续出现4,5..10次,代码就会非常冗余,不具备可拓展性。
select distinct l1.Num as ConsecutiveNums
from Logs l1,Logs l2,Logs l3
where l2.Id = l1.Id + 1
and l3.Id = l2.Id + 1
and l1.Num = l2.Num
and l2.Num = l3.Num
解法二:使用自定义变量进行条件判断
速度比解法一快,且适用于任一连续次数
select distinct Num ConsecutiveNums
from
(
select Num,
case
when @prev = Num then @count := @count+1 --如果与之前相等则+1
else (@prev := Num) and (@count := 1) --如果不相等 重新赋值,count变1
end CNT
from Logs,(select @prev := 0, @count := 0) t --变量初始化
) t
where t.CNT>=3
解法三:窗口函数
select distinct Num as ConsecutiveNums
from
(
select Num,Id,
lag(Id,2)over(partition by Num order by Id) as prev
from Logs
)t
where t.Id = t.prev + 2