[MY SQL] 세대별로 자식 수가 없는 개체 찾기

출처: [프로그래머스 스쿨 - 코딩테스트 연습 - MY SQL 문제] :  멸종위기의 대장균 찾기

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

문제(일부):  세대별로 자식이 없는 개체의 수와 세대를 출력

---

재귀쿼리 사용하기

풀이원본: https://school.programmers.co.kr/questions/76223

 

1) 재귀쿼리로 세대를 구분하는 임시테이블 생성
1-1) 임시테이블의 첫번째 쿼리는 초기값으로 1세대로 지정(PARENT_ID가 NULL = 1세대)

1-2) UNION ALL 아래의 두번째 쿼리는 재귀쿼리로 PARENT_ID와 매칭되는 ID가 없을 때 까지 반복하여 세대를 구분

*재귀쿼리에 대한 설명: https://o-s-o-jjj.tistory.com/35

 

2) 세대별 자식의 수를 COUNT하는 임시테이블 생성
2-1) 테이블을 SELF JOIN하여 ID와 매칭되는 PARENT_ID의 수를 COUNT,
       LEFT JOIN으로 PARENT_ID가 없는(자식개체가X)ID도 포함

3) 위 2개의 테이블을 ID를 기준으로 JOIN(세대별 COUNT 테이블은 서브쿼리로 자식 수가 0인 ID만 선택)

-- 재귀함수로 세대구분
WITH RECURSIVE GEN
AS (
    SELECT ID, PARENT_ID, 1 AS DEPTH
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    SELECT CHILD.ID, CHILD.PARENT_ID, GEN.DEPTH + 1
    FROM ECOLI_DATA CHILD
    JOIN GEN
    ON CHILD.PARENT_ID = GEN.ID
),

-- leftjoin으로 parent_id가 없는 id레코드 생성
-- ID별 자식의 수 COUNT
CHILD
AS (
    SELECT A.ID, COUNT(B.ID) NUM FROM ECOLI_DATA A
    LEFT JOIN ECOLI_DATA B
    ON A.ID=B.PARENT_ID
    GROUP BY A.ID
)

-- 재귀함수 테이블과 자식수 COUNT 테이블을 JOIN((자식수가 0인 테이블)
SELECT COUNT(*) COUNT, A.DEPTH GENERATION 
FROM GEN A
JOIN (SELECT * FROM CHILD WHERE NUM=0) B USING (ID)
GROUP BY A.DEPTH
ORDER BY GENERATION