[MySQL] 트리구조 탐색 쿼리 - 모든 부모 node 탐색

얼마전 면접을 보다가, 아래 그림과 같은 데이터 구조에서의 탐색 방법에 대한 DB 설계 및 쿼리 작성을 하시오 라는 질문에

최적의 답변을 하지 못해. 집에 와서 테스트 하였다.

 

질문의 핵심은 특정 node 를 선택했을때, 최상위 부모까지 올라가는 흐름을 한줄로 표현하는 것이었다.

 

ex) 그림20 > 그림17 > 그림13 > 그림10 > 그림9 > 그림3 > 그림1

 

데이터의 구조는 아래와 같다.

테이블로 옮기면,

일반적인 재귀호출 용 테이블의 구조가 된다. 여기에 아래 쿼리를 실행하면 

set @id := 26;        -- node id
set @L := 0;
 
 
SELECT @r AS _ID
    , (SELECT @r := pid FROM t_test WHERE id = _ID) AS _PARENT_ID
    , (@L := @L + 1) AS level
FROM ( SELECT @r := @id) AS vars, (SELECT * FROM t_test WHERE id < @id) AS h
WHERE @r <> 0;

그림과 같이 우리가 원하는 결과를 얻을 수 있다.

약간만 수정하면 두루 두루 쓸모가 많을것 같다.

이래서 실무자 면접은 참 재미있는것 같다. 내 실력을 평가받는 자리도 되고 그로인해 공부도 되고.

---------------------------------------------------------- ID , PID 가 문자형인경우 ----------------------------------------------------------

ID , PID 문자형일 경우 어떻게 수정해야 하는지에 대한 문의가 있어 관련 내용 공유 드립니다.

위 데이터에서 각 컬럼을 문자형으로 변경 후 앞에 a를 붙여 a1, a2, a3 형식으로 변경 하였습니다.

set @id := 'a26';         -- node id
set @L := 0;              -- level 컬럼의 시작 값, 0 입력시 1부터 1씩 증가된 값이 표시 됨
 
SELECT @r AS _ID
    , (SELECT @r := pid FROM t_test WHERE id = @r limit 1) AS _PARENT_ID
    , (@L := @L + 1) AS level
FROM ( SELECT @r := @id) AS vars, (SELECT * FROM t_test WHERE id != @id) AS h
WHERE @r <> 'a0';          -- > root node를 만나면 탐색을 중지하기 위함
-- WHERE @r <> '-';        -- > null 이 아닌 임이의 값 (예 : '-') 입력

출력 결과 : 

MSSQL 로 구현하면 >> https://devse.tistory.com/69