[리트코드] 180. Consecutive Numbers - MySQL

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

Consecutive Numbers - LeetCode

---

• 문제
  • 연속하는 3개의 num이 같은 num 출력하라

• 풀이 1 - JOIN

연속하는 3개의 숫자를 같은 행에 두기 + 3개의 값이 같은 것만 출력

SELECT *
FROM Logs AS l1
    INNER JOIN Logs AS l2 ON l1.id + 1 = l2.id
    INNER JOIN Logs AS l3 ON l2.id + 1 = l3.id

Logs 테이블 / 위 SQL 실행 결과

 

• 정답 코드 1

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM Logs AS l1
    INNER JOIN Logs AS l2 ON l1.id + 1 = l2.id
    INNER JOIN Logs AS l3 ON l2.id + 1 = l3.id
WHERE l1.num = l2.num AND l2.num = l3.num;

 

• 풀이 2 - 윈도우 함수

연속하는 3개의 숫자를 같은 행에 두기 + 3개의 값이 같은 것만 출력

SELECT num
    , LEAD(num,1) OVER (ORDER BY id) AS next_1
    , LEAD(num,2) OVER (ORDER BY id) AS next_2
FROM logs;

 

• 정답 코드 2

SELECT DISTINCT l.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 l
WHERE l.num = l.next_1 AND l.num = l.next_2;