题目如下:
思路一:
官方给的思路,假设Id的数字连续,连续出现三次意味着Id相连,值相同,将自身与自身与自身连接
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
显然这种方式效率低,显得笨重,翻题解的时候看到一个比较合适的
思路二:
设一个变量pre,和count用于记录前一个数与计录出现次数。
总体是当值与前一个数值pre相等时,count+=1,否则count=1,再把当前值赋给pre用于下一个比较。
select
@pre , Num, if(Num=@pre,@count:=@count+1,@count:=1) as nums ,@pre:=Num
from Logs,(select @pre:=null,@count:=1) as a;
结果如下:
可见结果如预期所示,再选择其中nums>=3的数字,投影一下
select distinct Num as ConsecutiveNums
from (
select
@pre , Num, if(Num=@pre,@count:=@count+1,@count:=1) as nums ,@pre:=Num
from Logs,(select @pre:=null,@count:=1) as a ) as ans
where nums>=3;
第二种执行用时更少一些,更灵活一些