[프로그래머스][MySQL] SELECT - 멸종위기의 대장균 찾기

문제

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

프로그래머스

 

풀이

• 재귀 쿼리 (WITH RECURSIVE)

WITH RECURSIVE SUB AS ( 
    -- Non-Recursive 문장( 첫번째 루프에서만 실행됨 )
    SELECT 1 AS n
    UNION ALL
    -- Recursive 문장(읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함)
    SELECT n + 1
    FROM SUB
    WHERE n < 3 -- 재귀 정지 조건
    )
SELECT * FROM SUB

실행 결과 : 

n

1

2

3

 

WITH RECURSIVE SUB AS (
    SELECT ID, PARENT_ID, 1 AS GENERATION
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    SELECT E.ID, E.PARENT_ID, GENERATION + 1 AS GENERATION
    FROM SUB
    JOIN ECOLI_DATA AS E
    ON E.PARENT_ID = SUB.ID)
    
SELECT COUNT(ID) AS COUNT, GENERATION
FROM SUB
WHERE ID NOT IN (SELECT DISTINCT PARENT_ID
                 FROM ECOLI_DATA
                 WHERE PARENT_ID IS NOT NULL)
GROUP BY GENERATION
ORDER BY GENERATION;