[SQL문제다시풀기] 멸종위기의 대장균 찾기 - 재귀 쿼리

문제

MYSQL

내가 작성한 오답

with recursive generation_tb as (
    -- 최상위 정의
    select id, parent_id, 1 as generation
    from ecoli_data
    where parent_id is null
    
    union all
    -- 하위 정의 (e의 부모 아이디와 g의 아이디가 같다 -> g가 부모테이블, e가 후손테이블-> 후손의 generation+1)
    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(id) COUNT, generation
from generation_tb
where not id in (select parent_id from ecoli_data)
group by generation
order by 2 asc

내가 작성한 정답

with recursive generation_tb as (
    -- 최상위 정의
    select id, parent_id, 1 as generation
    from ecoli_data
    where parent_id is null
    
    union all
    -- 하위 정의 (e의 부모 아이디와 g의 아이디가 같다 -> g가 부모테이블, e가 후손테이블-> 후손의 generation+1)
    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(id) COUNT, generation
from generation_tb
where id not in (select parent_id from ecoli_data where parent_id is not null)
group by generation
order by 2 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테이블과 join해준다.
        CTE_NAME AS cte ON t.some_column = cte.some_column
)
SELECT 
    *
FROM 
    CTE_NAME;