LeetCode MySQL-180. Consecutive Numbers

    AllenLi
    LeetCode MySQL

    Write an SQL query to find all numbers that appear at least three times consecutively.
    Return the result table in any order.

    寫一個 SQL Query來尋找所有至少連續出現三次的數值。
可任意排序結果。

    Table: Logs

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

    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.

    Solution:
    1. 選擇標題 ConsecutiveNums 為不重複的 num。
    2. 將 Logs 拆分成三個 t1, t2, t3 的表。
    3. 分別符合以下條件：

    - id 須為連號：
A:B:C = 1:2:3
((A + 1) = B) && ((B + 1) = C)

- num 皆須相同：
A:B:C = 1:1:1
(A = B) && (B = C)

    Code.1:

    SELECT DISTINCT(t1.num) AS ConsecutiveNums
    FROM Logs AS t1, Logs AS t2, Logs AS t3
WHERE t1.id + 1 = t2.id 
    AND t2.id + 1 = t3.id 
    AND t1.num = t2.num 
    AND t2.num = t3.num;

    Code.2:

    SELECT DISTINCT t1.num AS ConsecutiveNums
    FROM Logs AS t1
LEFT JOIN Logs AS t2 ON t1.id + 1 = t2.id
LEFT JOIN Logs AS t3 ON t2.id + 1 = t3.id
WHERE t1.num = t2.num
    AND t2.num = t3.num;

    newConsecutiveNumbers

    Related Posts