mariaDB 이용하여 oracle connet by 사용하기 (계층쿼리)

회사에서 oracle 쿼리를 mariaDB 문법으로 바꿔야하는 일이 있었다. 다른 문법이야 검색해서 바꾸면 되는데

 

계층쿼리인 oracle의 connect by문은 mariaDB 문법으로 전환 시 쿼리 길이가 매우 길어지며 이해하기 까다로웠다. 

 

그래서 기록으로 남기려 한다. 

 

내가 받은 업무는 조직도를 계층구조로 만드는 것이었는데 예를 들어 이런 것이다. 

 

 

이렇게 상위 root부터 시작해 하위루트, 즉 자식루트를 생성하는 계층도를 만들어야 했다. 

 

이 경우 oracle의 connet by문을 사용한다면  

 

SELECT 조직코드

        , 회사코드

        , 조직이름

        , 부모조직코드

        , LEVEL

        

FROM 조직도

WHERE 회사코드 = 5000

START WITH 부모조직코드 IS NULL

CONNECT BY PRIOR 조직코드 = 부모조직코드

 

이 쿼리를 실행시키면 아래와 같은 결과가 나온다.

 

 

 

이 결과를 보면 부모조직코드가 없는 ABC초콜릿은 가장 상위 조직이며 그 아래로 A, B, C부서가 존재하고

 

그 부서들 아래로 각 팀이 존재한다. LEVEL 은 계층을 숫자로 표현한 것이다. 가장 상위계층이 1이고

 

그 하위로 내려갈수록 1이 더해지는 구조이다. 

 

이렇게 간단한 쿼리문을 mariaDB는 지원하지 않는다. 때문에 매우 긴 쿼리를 짜야한다. 

 

mariaDB문법으로 위와 같은 결과가 나올 수 있도록 쿼리를 짜보자. 

 

 WITH RECURSIVE  CTE AS ( 

         SELECT 조직코드

                  , 회사코드

                  , 조직이름

                  , 부모조직코드

                  , 1 AS LEVEL

                 

        FROM 조직도

        WHERE 회사코드 = 5000

 

        UNION ALL

 

        SELECT  A.조직코드

                  , A.회사코드

                  , A.조직이름

                  , A.부모조직코드

                  , 1 + B.LEVEL AS LEVEL

                  

        FROM 조직도 A

        INNER JOIN CTE B ON A.부모조직코드 = B.조직코드

)

 

SELECT DISTINCT  A.조직코드

                       , A.회사코드

                       , A.조직이름

                       , A.부모조직코드

                       , A. LEVEL

                      

FROM CTE A

LEFT OUTER JOIN CTE B ON B.부모조직코드 = A.조직코드

 

CTE는 가상 테이블의 이름이다. mariaDB에서는 이처럼 가상의 테이블을 만들어 조인을 한 후

 

계층구조를 표현할 수 있다.