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