[MySQL] 재귀 쿼리 (WITH RECURSIVE)

신규 프로젝트를 진행하면서 회원을 hierarchy구조로 사용해야해서 self join 을 쓰지않고 조회하는 방법을 찾아봤다.

Oracle

• Start With ~ : 최상위 조건
• Connect By Prior ~ : 부모와 자식 관계로 연결할 데이터 매핑

# 판매자 계층조회 쿼리

SELECT USERNO,NAME,DECODE(LEVEL,1,'대리점',2,'매니저',3,'영업사원')
FROM T_DEALER
WHERE SUPPLIER_NO IS NOT NULL
START WITH UPPER_DEALER_NO IS NULL -- 최상위 계층 조회 조건
CONNECT BY PRIOR USERNO = UPPER_DEALER_NO -- 매핑 조건
ORDER BY LEVEL;

 

MySql

• Oracle은 간단하게 조회가능했으나 MySQL은 With Recursive를 통해 재퀴쿼리를 작성해야한다
• WITH RECURSIV [table_name] AS ~ : 재귀를 통해 데이터를 쌓아 보여줄 가상의 테이블
• Union ALL : 유니온을 필수로 써야한다.
  • 최상위 계층 조회 쿼리 Union ALL 재귀를 통해 매핑할 조회 조건 작성

# 프로그래머스 LV5. 멸종위기의 대장균 찾기
-- 가상의 테이블 선언
WITH RECURSIVE WITH_ECOLI_DATA(LVL,ID,PARENT_ID) AS
(
	  -- 최상위 계층 조회
    SELECT 1 AS LVL,ID,PARENT_ID
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    
    UNION ALL
    -- 최상위 기준으로 자식 매핑
    SELECT LVL +1 AS LVL,A.ID,A.PARENT_ID
    FROM ECOLI_DATA A, WITH_ECOLI_DATA B
    WHERE B.ID = A.PARENT_ID
)
-- 위에 선언한 가상의 테이블을 통해 최종 조회
SELECT COUNT(1) AS COUNT,LVL as GENERATION
FROM WITH_ECOLI_DATA A
WHERE (SELECT COUNT(1) FROM WITH_ECOLI_DATA WHERE A.ID = PARENT_ID) = 0
GROUP BY LVL
ORDER BY GENERATION
;