SELECT DISTINCT Num ConsecutiveNumsFROM(SELECT Num,caseWHEN @prev = Num then @cnt:=@cnt+1-- when clause 1WHEN (@prev:= Num) is not nullthen @cnt:=1-- when clause 2end cntFROM Logs, (SELECT @prev:=null, @cnt:=null) t1) t2WHERE cnt >=3;
when clause 1 在第一行必跳过
when 2 条件总是true,执不执行完全取决于when 1 是否为true:第一条false了,就执行 @cnt:=1;第一条 true了,压根到不了这一条。
Table Schema
CreatetableIfNotExists Logs (Id int, Num int);Truncatetable Logs;insert into Logs (Id, Num) values ('1', '1');insert into Logs (Id, Num) values ('2', '1');insert into Logs (Id, Num) values ('3', '1');insert into Logs (Id, Num) values ('4', '2');insert into Logs (Id, Num) values ('5', '1');insert into Logs (Id, Num) values ('6', '2');insert into Logs (Id, Num) values ('7', '2');