180. Consecutive Numbers.md

180. Consecutive Numbers

Question

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.

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

二刷

1. 实际上一张表完全可以和自身进行匹配。
2. 我们可以使用该表和自身进行匹配。

# Write your MySQL query statement below
SELECT DISTINCT l1.Num as ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3
WHERE l2.Id = l1.Id - 1 and l3.Id = l1.Id - 2 and l1.Num = l2.Num and l3.Num = l1.Num;

1. 使用变量
2. 记得FROM以后一定是接着一张表， 所以即使是我们查找出来的表结构，我们也要使用AS字段将查找结果变成一张新的表结构。

# Write your MySQL query statement below
SELECT DISTINCT Num AS ConsecutiveNums
FROM (
    SELECT Num, @count := IF(Num = @pre, @count + 1, 1) AS cc, @pre := Num
    FROM Logs, (SELECT @count := 0, @pre := -1) AS init
) AS t
WHERE t.cc > 2;

引用

1. leetcode 180. Consecutive Numbers