Consecutive Numbers

Consecutive Numbers

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

Example

±—±----+
| 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 |
±----------------+

Solution

@左联比内联速度更快，但左联的过滤条件必须写到where而不是on里面
SELECT DISTINCT L1.Num AS ConsecutiveNums FROM Logs L1
  LEFT JOIN Logs L2 ON L2.Id=L1.Id+1
  LEFT JOIN Logs L3 ON L3.Id=L2.Id+1
 WHERE L1.Num=L2.Num AND L2.Num=L3.Num

# Write your MySQL query statement below
SELECT DISTINCT L1.Num AS ConsecutiveNums FROM Logs L1
 INNER JOIN Logs L2 ON L2.Id=L1.Id+1 AND L2.Num=L1.Num
 INNER JOIN Logs L3 ON L3.Id=L2.Id+1 AND L3.Num=L2.Num