LeetCode Database Consecutive Numbers 180

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.

大体意思

写 SQL 查询出连续出现至少 3 次的 Num

自己的解法

SELECT DISTINCT L1.Num AS ConsecutiveNums
FROM Logs L1
	JOIN Logs L2 ON L1.Id + 1 = L2.Id
	JOIN Logs L3 ON L1.Id + 2 = L3.Id
WHERE L1.Num = L2.Num AND L1.Num = L3.Num

很传统连表查询，但是有坑的地方，就是依靠的时 Id，所以局限是 Id 要连贯

别人的解法

首先，增加一个 rank 字段，记录序号，初始值为 1，当后一个值与前一个值相等时，序号加 1。之后，把所有 rank 值大于等于 3 的都检索出来，再去重即可。

SELECT DISTINCT(Num) AS ConsecutiveNums FROM
(SELECT Id,Num,
@Rank:=IF(@prevNum != Num,1,@Rank+1) AS Rank,
@prevNum:=Num
FROM Logs) t,
(SELECT @Rank:=0,@prevNum:=NULL) r
WHERE t.Rank >= 3;

自己的理解

(SELECT @Rank:=0,@prevNum:=NULL) r

是个巧妙的地方，对变量进行了初始化

Reference:
【leetcode Database】180. Consecutive Numbers - Kevin_zhai 的博客