[MySQL][LeetCode][Medium] 180. Consecutive Numbers

心得

這題要找出連續三次重複出現的數字,我原本一直在想如果中間有斷層(中間有資料被刪除)的話是否要先自己排序一次,結果看了一下Top Solutions才發現根本不用考慮這個問題,直接ID+1尋找下筆資料即可,既然這麼單純的話也沒什麼問題了。

問題

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

+----+-----+
>
> | 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.
答案

  1. 方法對,但會TimeOut
1
2
3
4
5
6
7
8
9
10

# Write your MySQL query statement below
SELECT DISTINCT(a.Num) AS ConsecutiveNums
FROM Logs AS a
WHERE ( SELECT z.Num
FROM Logs AS z
WHERE z.Id = a.Id + 1) = a.Num
AND ( SELECT z.Num
FROM Logs AS z
WHERE z.Id = a.Id + 2) = a.Num
  1. 通過
1
2
3
4
5
6
7
8
9
10

# Write your MySQL query statement below
SELECT DISTINCT(a.Num) AS ConsecutiveNums
FROM Logs AS a
JOIN Logs AS b
ON a.Id = b.Id - 1
JOIN Logs AS c
ON b.Id = c.Id - 1
WHERE a.Num = b.Num
AND b.Num = c.Num