[mysql] hierarchy 구조에서 recursive 쿼리의 사용

hierarchy 구조에서 recursive 쿼리의 사용

• Desc
  • hierarchy: 계층
  • recursive: 재귀
Example
• hierarchy 구조의 샘플 데이터 생성

      CREATE TABLE recursive_test_table
      (
          id INTEGER NOT NULL,
          name VARCHAR(128) NULL,
          parent_id INTEGER NULL,
          CONSTRAINT pk_recursive PRIMARY KEY (id)
      );
  
      INSERT INTO recursive_test_table (id, name, parent_id) VALUES (1, '봉근', NULL);
      INSERT INTO recursive_test_table (id, name, parent_id) VALUES (2, '봉근 A', 1);
      INSERT INTO recursive_test_table (id, name, parent_id) VALUES (3, '봉근 B', 1);
      INSERT INTO recursive_test_table (id, name, parent_id) VALUES (4, '봉근 C', 1);
      INSERT INTO recursive_test_table (id, name, parent_id) VALUES (5, '봉근 A2', 2);
      INSERT INTO recursive_test_table (id, name, parent_id) VALUES (6, '봉근 B2', 3);
      INSERT INTO recursive_test_table (id, name, parent_id) VALUES (7, '봉근 B3', 6);
      INSERT INTO recursive_test_table (id, name, parent_id) VALUES (8, '봉근 B4', 7);
  

recursive 쿼리 작성

• 1을 기준으로 자식 노드를 뽑는 쿼리 실행

  SELECT id, name, parent_id
  FROM
      (
          SELECT * FROM recursive_test_table
          ORDER BY parent_id, id) products_sorted,
          (
              SELECT @pv := '1'
          ) initialisation
  WHERE find_in_set(parent_id, @pv) > 0
  AND @pv := concat(@pv, ',', id);
  

  

  
  

• 7을 기준으로 부모 노드를 모두 뽑는 쿼리 실행

    SELECT
        CONCAT(REPEAT('', level  - 1), d.name) AS name,
        d.id,
        d.parent_id,
        d.name,
        func.level
    FROM
    (
        SELECT B._id, @lv2 := @lv2 + 1 AS level
        FROM (
            SELECT @r AS _id,
            (
                SELECT @r := parent_id
                FROM recursive_test_table
                WHERE Id = _id
            ) AS parent,
            @l := @l +1 AS lv
            FROM
            (
                SELECT @r := 7,
                @l := 0
            ) vars,
            recursive_test_table d
            WHERE @r <> 0
            ORDER BY lv DESC
        ) B,
        (
            SELECT @lv2 := 0
        ) vars2
    ) func
    LEFT JOIN recursive_test_table d ON func._id = d.Id;