[SQL] 쿼리로 트리구조 출력하기

조직도나 댓글-대댓글처럼 트리 형태의 구조를 갖는 데이터들을 트리 depth에 맞게 출력 할 수 있다.

mysql

WITH RECURSIVE CommentTree AS (
    SELECT id, parent_id, content, 0 AS depth
    FROM comment
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.parent_id, c.content, ct.depth + 1
    FROM comment c
    JOIN CommentTree ct ON c.parent_id = ct.id
)

SELECT *
FROM CommentTree
ORDER BY COALESCE(parent_id, id) ASC, id asc;

oracel

SELECT
    c.id,
    c.parent_id,
    c.content,
    LEVEL - 1 AS depth
FROM
    comment c
START WITH
    c.parent_id IS NULL
CONNECT BY
    PRIOR c.id = c.parent_id
ORDER SIBLINGS BY
    c.parent_id NULLS FIRST,
    c.id;