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

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

 

Question

 

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

Point

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

 

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, G.LV + 1
        FROM ECOLI_DATA E
        INNER JOIN GENERATION G
        ON E.PARENT_ID = G.ID
)
SELECT ID
    FROM GENERATION
    WHERE LV = 3
    ORDER BY ID

 

now me

On my github