출처: https://leetcode.com/problems/consecutive-numbers/description/
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
In SQL, id is the primary key for this table.
id is an autoincrement column starting from 1.
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.
내가 한 풀이
SELECT DISTINCT num AS ConsecutiveNums
FROM (
SELECT
num,
LEAD(num, 1) OVER (ORDER BY id) AS next_1,
LEAD(num, 2) OVER (ORDER BY id) AS next_2
FROM Logs
) AS temp
WHERE num = next_1 AND next_1 = next_2;
트러블 슈팅
일단 LEAD 함수 사용법 좀 익히자 쓸일은 많을지 몰라도.
