leetcode-database-180. Consecutive Numbers

Write a SQL query to find all numbers that appear at least three times consecutively.

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

For example, given the above  Logs table,  1 is the only number that appears consecutively for at least three times.

题意：查询出连续出现3次的数字

思路：

定义参数preNum记录前一个Number的值，curRank记录目前连续出现的次数

@curRank:=@curRank +if(@preNum=Num,0,1)

如果当前的Number值等于preNum，那么curRank+1

select Num,@curRank:=@curRank + if(@preNum=Num,0,1) As rank,@preNum:=Num  from Logs s,(select @curRank:=0) r,(select @preNum:=NULL) p order by id ASC

+------+-------+
| Num | rank |
+------+-------+
| 1       |  1      |
| 1       |  1      |
| 1       |  1      |
| 2      |  2     |
| 1       |  3     |
| 2      |  4     |
| 2      |  4     |
+------+-------+

select Num,count(rank) as cnt

from(select Num,@curRank:=@curRank + if(@preNum=Num,0,1) As rank,@preNum:=Num  from Logs s,(select @curRank:=0) r,(select @preNum:=NULL) p order by id ASC) t

group by rank

+------+-------+
| Num | cnt    |
+------+-------+
| 1       |  3       |
| 2      |   1       |
| 1       |   1       |
| 2      |  2       |
+------+-------+

最终sql语句：

select distinct Num as ConsecutiveNums from (select Num,count(rank) as cnt

from(select Num,@curRank:=@curRank + if(@preNum=Num,0,1) As rank,@preNum:=Num  from Logs s,(select @curRank:=0) r,(select @preNum:=NULL) p order by id ASC) t

group by rank having cnt>=3) n;