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

Dec 22, 2024

문제

school.programmers.co.kr

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

내가 작성한 오답


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;

→ 문제에서 count와 generation을 출력하라고 했는데 generation과 count를 출력해서 순서가 맞지 않아 오답으로 처리됐던 것 같다. 출력 결과의 순서를 바꾸니 정답처리 됐다.

재귀 쿼리 사용법


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;

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;