题目描述
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+
代码
/* Write your T-SQL query statement below */
selectdistinct num as consecutivenums
from(select a.num as num,case when a.num=b.num and b.num = c.num then 1else 0end as is_consecutivefrom logs aleft join logs bon a.id+1 = b.idleft join logs con a.id+2 = c.idwhere c.id is not null)t
where is_consecutive=1##问题：如果需要找连续十次出现的数字，那需要十张表相连，不太科学，可以尝试使用用户变量
# Write your MySQL query statement below
select distinct num as consecutivenums
from (select num,case when @prev = num then @count := @count + 1when (@prev := num) is not null then @count := 1end as cntfrom logs, (select @prev := null,@count := null) as t) temp
where temp.cnt >= 3