[Programmers Lv.4] 특정 세대의 대장균 찾기 - 301650

세대 찾기(LEVEL)을 간단하게 JOIN을 이용하여 풀기 !

 

Question

 

3세대의 대장균의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 이때 결과는 대장균의 ID 에 대해 오름차순 정렬해주세요.

Point

• Code 1 : JOIN의 코드 이해를 위한 시각화 자료.
• 2세대 -> JOIN 1번, 3세대 -> JOIN 2번.

 

Code (After 2509)

 

with recursive cte as(
    select parent_id, id, 1 as lv
    from ECOLI_DATA 
    where parent_id is null
    
    union all
    
    select c.parent_id, c.id, lv + 1
    from cte p
    join ECOLI_DATA c on p.id = c.parent_id
    where lv < 3
)
select id
from cte
where lv = 3
order by 1
;

 

Code 1 : JOIN

 

SELECT A.ID
    FROM ECOLI_DATA AS A
    JOIN ECOLI_DATA AS B ON A.PARENT_ID = B.ID
    JOIN ECOLI_DATA AS C ON B.PARENT_ID = C.ID
    WHERE C.PARENT_ID IS NULL
    ORDER BY 1;

 

Code 2 : My code (CTE)

 

WITH RECURSIVE GENERATION AS(
    SELECT ID, PARENT_ID, 1 AS LV
        FROM ECOLI_DATA
        WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT E.ID, E.PARENT_ID, LV + 1
        FROM ECOLI_DATA E
        JOIN GENERATION G ON E.PARENT_ID = G.ID
)
SELECT ID
    FROM GENERATION
    WHERE LV = 3
    ORDER BY ID

 

now me

On my github