leetCode之 连续出现的数字 sql语句

1.题目
有表Logs，要求编写一个 SQL 查询，查找所有至少连续出现三次的数字

2.我的答案
select DISTINCT Num as ConsecutiveNums
from
(
select num ,
case
when @agent = num then @count := @count + 1
when (@agent := num) is not null then @count :=1
end as cnt
from Logs , (select @agent := null , @count := null) as d
) as tmp
where tmp.cnt>=3

3.知识点
(1)子查询必须加别名，不加报错
(2)@name 是声明一个临时变量
(3)在条件判断语句中 ’ = ’ 是用来逻辑判断的，’ := ’ 是用来赋值的
(4)case用法在 https://mp.csdn.net/postedit/90413618

4.语句解析
(1)首先在Logs每条记录中加个字段记录出现的次数
select num ,
case
when @agent = num then @count := @count + 1 #连续出现时count会渐增
when (@agent := num) is not null then @count :=1 #为每一个第一次出现的count都初始化为1
end as cnt
from Logs , (select @agent := null , @count := null) as dec
效果如下

(2)拿出出现次数不少于3的num
select DISTINCT Num as ConsecutiveNums
from
(
select num ,
case
when @agent = num then @count := @count + 1
when (@agent := num) is not null then @count :=1
end as cnt
from Logs , (select @agent := null , @count := null) as d
) as tmp
where tmp.cnt>=3