계층형 질의

 

 

UNION

• 2개의 SELECT 된 결과를 병합한다.
• Default는 중복을 제거하고 정렬한다.

UNION ALL

• 2개의 SELECT 된 결과물을 병합한다.
• 중복을 제거하지 않고 정렬하지 않는다.

 

계층형

 

데이터 테이블을 구성할 때 특정 상황에서 내부적으로 부모 자식 관계를 갖는 형식으로 구성할 경우가 있다.

• 무한 댓글 구조 , status 코드화 등등

해당 구조에서는 테이블의 pk 값을 부모 id를 컬럼 값으로 갖도록 하여 계층형 테이블 구조로 구성된다.

 

이에 대한 구조와 조회는 아래와 같다.

 

- Member 테이블 구조

id	parents_id
1000	NULL
1001	1000
1002	1001
1003	1002
1004	1002
1005	1000
1006	1005
1007	1006

 

- 계층형 조회

WITH RECURSIVE NEWTABLE(id, parents_id, lvl)
AS(
    SELECT id, parents_id, 0 as lvl
    FROM MEMBER
    WHERE manager_id IS NULL

    UNION ALL

    SELECT a.id , a.parents_id, b.lvl+1
    FROM MEMBER AS a
    JOIN NEWTABLE AS b
    ON a.parents_id = b.id
)

SELECT id, parents_id, lvl
FROM NEWTABLE
ORDER BY parents_id ASC, lvl ASC;

 

- 계층형 조회 결과

id	parents_id	lvl
1000	NULL	0
1001	1000	1
1002	1001	2
1003	1002	3
1004	1002	3
1005	1000	1
1006	1005	2
1007	1006	3

 

 

무한 댓글 예시

 

- 쿼리문

WITH RECURSIVE pibonacci(lv , comment_id, parent_comment_id ,comment_text, root_path) 
AS(
	SELECT 
    0 AS lv
    , comment_id
    , parent_comment_id
    , comment_text
    , CAST(concat('/',comment_id)AS CHAR(255)) AS root_path
	FROM comments 
    WHERE parent_comment_id IS NULL
	UNION ALL
	SELECT 
		b.lv+1
		, a.comment_id
		, a.parent_comment_id
		, a.comment_text
		, concat(b.root_path, '/' , a.comment_id) AS root_path
	FROM comments AS a
	JOIN pibonacci AS b
	ON a.parent_comment_id = b.comment_id
    )
    
SELECT lv , comment_id, parent_comment_id , CONCAT(LPAD('', 4*lv, ' '),comment_text) AS comment_text ,root_path 
FROM pibonacci
ORDER BY root_path, lv ASC;

 

 

- 계층형 조회 결과

lv	comment_id	parent_comment_id	comment_text	root_path
0	5001		강의 자료 좀 받을 수 있을까요?	/5001
1	5003	5001	공유드릴게요!	/5001/5003
2	5004	5003	감사합니다!	/5001/5003/5004
0	5002		파이썬 강의 잘 보고 갑니다!	/5002
1	5005	5002	다음 영상도 기대해주세요!	/5002/5005