[SQL문제풀기] 멸종위기의 대장균 찾기

문제

내가 작성한 오답

WITH RECURSIVE GENERATION_TB 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_TB G ON E.PARENT_ID = G.ID
)
SELECT G.GENERATION, COUNT(*) COUNT
FROM GENERATION_TB G
JOIN ECOLI_DATA E ON G.ID = E.ID
WHERE G.ID NOT IN (SELECT PARENT_ID FROM GENERATION_TB WHERE PARENT_ID IS NOT NULL)
GROUP BY G.GENERATION
ORDER BY GENERATION ASC;

내가 작성한 정답

WITH RECURSIVE GENERATION_TB 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_TB G ON E.PARENT_ID = G.ID
)
SELECT COUNT(G.ID) COUNT, G.GENERATION
FROM GENERATION_TB G
JOIN ECOLI_DATA E ON G.ID = E.ID
WHERE G.ID NOT IN (SELECT PARENT_ID FROM GENERATION_TB WHERE PARENT_ID IS NOT NULL)
GROUP BY G.GENERATION
ORDER BY GENERATION ASC;

재귀 쿼리 사용법

WITH RECURSIVE CTE_NAME AS (
    -- Anchor member (기본 쿼리)
    SELECT 
        columns
    FROM 
        your_table
    WHERE 
        condition_for_anchor_member

    UNION ALL

    -- Recursive member (재귀 쿼리)
    SELECT 
        columns
    FROM 
        your_table AS t
    JOIN 
        CTE_NAME AS cte ON t.some_column = cte.some_column
)
SELECT 
    *
FROM 
    CTE_NAME;