README_EN.md

180. Consecutive Numbers

中文文档

Description

Table: Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id is the primary key for this table.
id is an autoincrement column.

 

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

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
Output: 
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
Explanation: 1 is the only number that appears consecutively for at least three times.

Solutions

SQL

select distinct(Num) as ConsecutiveNums from Logs Curr where
    Num = (select Num from Logs where id = Curr.id - 1) and
    Num = (select Num from Logs where id = Curr.id - 2)

# Write your MySQL query statement below
SELECT DISTINCT l1.num AS ConsecutiveNums
FROM
    logs AS l1,
    logs AS l2,
    logs AS l3
WHERE
    l1.id = l2.id - 1
    AND
    l2.id = l3.id - 1
    AND
    l1.num = l2.num
    AND
    l2.num = l3.num