[SQLD] 계층형 질의(Hierarchical query), start with, connect by prior, order siblings by

                                    계층형 데이터 구조 정리

회사
 |--- IT부서
        |--- 개발팀
               |----- 백엔드
               |----- 프론트엔드
        |--- 디자인 팀

                                    select
    LPAD(' ', 3 * (LEVEL)) || name as department_hierarchy,  -- form을 지정
    id,
    parent_id,
    LEVEL
FROM
    hierarchy_departments
start with
     parent_id is null    -- 어디서 시작할건지?
connect by
     prior id = parent_id -- 자식: id, 부모: parent_id
order siblings by         -- name 기준으로 가, 나, 다 순서로 정렬
    name;

                                    💡 LPAD는 이해를 쉽게하기 위하여 임의로 설정한 것인데, SQLD에서는 LPAD 설정없이 나오기 때문에
기본으로 출력해보는 것이 필요합니다.

                                    select
    LPAD(' ', 3 * (LEVEL)) || name as department_hierarchy,
    id,
    parent_id,
    LEVEL
FROM
    hierarchy_departments
start with
     name = '백엔드'   -- 여기서 시작해
connect by
     id = prior parent_id -- 역방향으로 올라갈거야.
order siblings by
    name;

                                    CREATE TABLE hierarchy_departments(
    id NUMBER(19) PRIMARY KEY,
    name VARCHAR2(20) NOT NULL,
    parent_id NUMBER(19)
);

                                    INSERT ALL
    INTO hierarchy_departments (id, name, parent_id) VALUES (1, '회사', NULL)
    INTO hierarchy_departments (id, name, parent_id) VALUES (2, 'IT 부서', 1)
    INTO hierarchy_departments (id, name, parent_id) VALUES (3, '개발팀', 2)
    INTO hierarchy_departments (id, name, parent_id) VALUES (4, '디자인팀', 2)
    INTO hierarchy_departments (id, name, parent_id) VALUES (5, '프론트엔드', 3)
    INTO hierarchy_departments (id, name, parent_id) VALUES (6, '백엔드', 3)
SELECT * FROM dual;

                                    select
    LPAD(' ', 3 * (LEVEL)) || name as department_hierarchy,
    id,
    parent_id,
    LEVEL
FROM
    hierarchy_departments
start with
     parent_id is null
connect by
     prior id = parent_id
order siblings by
    name;