[recursive] 멸종 위기의 대장균 찾기

링크

https://school.programmers.co.kr/learn/courses/30/lessons/301651

프로그래머스

 

문제

정답: Not Exists를 활용한 방법

WITH RECURSIVE Generation AS (
    SELECT ID, PARENT_ID, 1 AS GENERATION
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT e.ID, e.PARENT_ID, g.GENERATION + 1
    FROM ECOLI_DATA e
    JOIN Generation g ON e.PARENT_ID = g.ID
)
SELECT COUNT(*) AS COUNT, GENERATION
FROM Generation g
WHERE NOT EXISTS (
    SELECT 1 
    FROM ECOLI_DATA e 
    WHERE e.PARENT_ID = g.ID
)
GROUP BY GENERATION
ORDER BY GENERATION;

정답: Left join을 활용한 방법

WITH RECURSIVE Generation AS (
    SELECT ID, PARENT_ID, 1 AS GENERATION
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT e.ID, e.PARENT_ID, g.GENERATION + 1
    FROM ECOLI_DATA e
    JOIN Generation g ON e.PARENT_ID = g.ID
)
SELECT COUNT(*) AS COUNT, GENERATION
FROM Generation g
LEFT JOIN ECOLI_DATA e ON g.ID = e.PARENT_ID
WHERE e.ID IS NULL
GROUP BY GENERATION
ORDER BY GENERATION;