[LeetCode] 180. Consecutive Numbers - SQL

링크: https://leetcode.com/problems/consecutive-numbers/

Table: Logs

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

---

문제풀이

목표: 연달아 3번 연속 나타나는 숫자를 찾아라

최종코드

SELECT DISTINCT l1.num as ConsecutiveNums
FROM Logs l1
    JOIN Logs l2 ON l1.id = l2.id - 1
    JOIN Logs l3 ON l1.id = l3.id - 2
WHERE l1.num = l2.num AND l2.num = l3.num;

설명

FROM 절

FROM Logs l1
    JOIN Logs l2 ON l1.id = l2.id - 1
    JOIN Logs l3 ON l1.id = l3.id - 2

• Logs 테이블에서 첫 번째 별칭 l1을 사용하여 테이블을 조회
  • l1과 l2를 조인하는데, 여기서 l2의 id는 l1보다 1 작아야 한다.
  • 마찬가지로 l3와 조인하며, l3의 id는 l1보다 2 작다.

WHERE 절

WHERE l1.num = l2.num AND l2.num = l3.num;

• WHERE 절로 세 개의 숫자가 모두 같은 것만 필터링

SELECT 절

SELECT DISTINCT l1.num as ConsecutiveNums