MySQL/180. Consecutive Numbers 连续出现的数字
题目
代码部分一(670ms)
SELECT
DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1
JOIN Logs l2 ON l1.id=l2.id-1
JOIN Logs l3 ON l2.id=l3.id-1
WHERE l1.Num=l2.Num AND l2.Num=l3.Num;
代码部分二(578ms)
SELECt
DISTINCT l1.num AS ConsecutiveNums
FROM logs l1,logs l2, logs l3
WHERE l1.Id+2=l3.Id AND l2.Id+1=l3.Id AND l1.num=l2.num AND l2.num = l3.num;
代码部分三(419ms)
select distinct Num as ConsecutiveNums
from (
select Num,
case
when @prev = Num then @count := @count + 1
when (@prev := Num) is not null then @count := 1
end as CNT
from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3
代码部分四(237ms)
SELECT distinct num as ConsecutiveNums FROM(
SELECT id, num,
@pre := @cur,
@cur := num,
@rep_ct := IF(@pre = @cur, @rep_ct + 1, 1) as rep_ct
FROM `Logs` l, (SELECT @pre := null, @cur := 0, @rep_ct := 1) init
) temp WHERE rep_ct >= 3