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

재귀 쿼리

silver

May 25, 2025

Contents

문제 MYSQL

문제

school.programmers.co.kr

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

MYSQL

내가 작성한 정답


with recursive gen as (
select ID, PARENT_ID, 1 as GENERATION
from ECOLI_DATA
where PARENT_ID is null

union all

select c.ID, c.PARENT_ID, p.GENERATION+1
from ECOLI_DATA c
join gen p on c.PARENT_ID = p.ID
)
select count(ID) COUNT,GENERATION
from gen
where ID not in (select PARENT_ID from gen where PARENT_ID is not null)
group by 2
order by 2;

재귀 쿼리 사용법


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;

SQL문제풀기

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

재귀 쿼리

silver

May 25, 2025

Contents

문제 MYSQL

문제

school.programmers.co.kr

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

MYSQL

내가 작성한 정답


with recursive gen as (
select ID, PARENT_ID, 1 as GENERATION
from ECOLI_DATA
where PARENT_ID is null

union all

select c.ID, c.PARENT_ID, p.GENERATION+1
from ECOLI_DATA c
join gen p on c.PARENT_ID = p.ID
)
select count(ID) COUNT,GENERATION
from gen
where ID not in (select PARENT_ID from gen where PARENT_ID is not null)
group by 2
order by 2;

재귀 쿼리 사용법


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;