180. Consecutive Numbers

Medium (Trung bình) SQL 🔗 Xem trên LeetCode

📋 Đề Bài

Table: Logs

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

 

Find all numbers that appear at least three times consecutively.

Return the result table in any order.

The 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.

💻 Lời Giải

SQL 0180-consecutive-numbers.sql
# Write your MySQL query statement below

SELECT DISTINCT num AS ConsecutiveNums 
FROM Logs
WHERE (id+1, num) IN (SELECT id, num FROM logs) AND (id+2, num) IN (SELECT id, num FROM logs)