[LeetCode] Consecutive Numbers 풀이

[문제]

Consecutive Numbers 문제 바로 가기 

Consecutive Numbers - LeetCode

 

 

[풀이]

 

- 셀프조인 2번하는 방식으로 풀이 

 

(1)

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM logs l1
     INNER JOIN logs l2 ON L1.id = l2.id - 1
     INNER JOIN logs l3 ON l2.id = l3.id - 1
WHERE l1.num = l2.num 
  AND l1.num = l3.num

(2)

SELECT DISTINCT l1.num AS ConsecutiveNums
FROM logs l1
     INNER JOIN logs l2 ON L1.id + 1 = l2.id 
     INNER JOIN logs l3 ON l1.id + 2 = l3.id 
WHERE l1.num = l2.num 
  AND l1.num = l3.num

 

- MS SQL 윈도우 함수 풀이

SELECT DISTINCT co.num AS ConsecutiveNums 
FROM(
    SELECT num
     , LEAD(num) OVER (ORDER BY id) AS lead 
     , LEAD(num, 2) OVER (ORDER BY id) AS lead2
    FROM Logs) co
WHERE co.num = co.lead 
AND co.num = co.lead2