Oracle에서는 CONNECT BY PRIOR, START WITH를 사용하여

자신의 ID와 연결된 부모 ID를 찾아가 계층적으로 쿼리결과를 뽑을 수 있다.

오라클에서만 제공하는 구문이다.


1
2
3
4
5
6
7
-- UPMENU_ID에 연결된 MENU_ID가 계층적으로 표현된다.
 
 SELECT MENU_ID
      , UPMENU_ID
   FROM SYS_MENUINFO A
CONNECT BY PRIOR MENU_ID = UPMENU_ID 
  START WITH UPMENU_ID = '00000'
cs


오라클 쿼리 결과



MySQL에서 위와 같은 계층형 쿼리로 구현하고자 한다면 Oracle에서 제공하는 구문을 사용할 수 없다.

아래는 테스트로 구현할 계층형 형태의 쿼리 결과이다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
동물
 ┗말
   ┗얼룩말
   ┗조랑말
      ┗망아지
 
 ┗닭
   ┗흰닭
      ┗흰병아리
           ┗흰달걀
 
   ┗검은닭
      ┗검은병아리
           ┗검은달걀
cs




MySQL에서 계층형 쿼리 구문이 따로 없어 

함수를 만들어 재귀함수를 사용해 구현해본다.


ANIMAL 테이블을 생성한다.


1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `ANIMAL` (
 
`id` int(10unsigned NOT NULL AUTO_INCREMENT,
 
`p_id` int(10unsigned default '0',
 
`nm` varchar(50),
 
primary key(`id`)
 
ENGINE=InnoDB DEFAULT CHARSET=utf8
cs


ANIMAL 테이블에 데이터를 넣어준다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
insert into ANIMAL(p_id, nm) values ( 0'동물');
 
insert into ANIMAL(p_id, nm) values ( 1'말');
 
insert into ANIMAL(p_id, nm) values ( 1'닭');
 
insert into ANIMAL(p_id, nm) values ( 2'얼룩말');
 
insert into ANIMAL(p_id, nm) values ( 2'조랑말');
 
insert into ANIMAL(p_id, nm) values ( 3'흰닭');
 
insert into ANIMAL(p_id, nm) values ( 3'검은닭');
 
insert into ANIMAL(p_id, nm) values ( 5'망아지');
 
insert into ANIMAL(p_id, nm) values ( 6'흰병아리');
 
insert into ANIMAL(p_id, nm) values ( 7'검은병아리');
 
insert into ANIMAL(p_id, nm) values ( 9'흰달걀');
 
insert into ANIMAL(p_id, nm) values ( 10'검은달걀');
cs


함수 fnc_hierarchi 를 만들어준다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
DROP FUNCTION IF EXISTS fnc_hierarchi;
 
DELIMITER $$
 
CREATE FUNCTION  fnc_hierarchi() RETURNS INT
 
NOT DETERMINISTIC
 
READS SQL DATA
 
BEGIN
 
    DECLARE v_id INT;
    DECLARE v_parent INT;    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
 
    SET v_parent = @id;
    SET v_id = -1;
 
    IF @id IS NULL THEN
        RETURN NULL;
    END IF;
 
    LOOP
    
    SELECT MIN(id)
      INTO @id 
      FROM ANIMAL
     WHERE p_id = v_parent
       AND id > v_id;
 
    IF (@id IS NOT NULL) OR (v_parent = @start_with) THEN
       SET @level = @level + 1;
    RETURN @id;
    END IF;
    
    SET @level := @level - 1;
 
    SELECT id, p_id
      INTO v_id , v_parent 
        FROM ANIMAL
       WHERE id = v_parent;
   
    END LOOP;
 
END
 
$$
 
DELIMITER ;
cs


조회해본다.


1
2
3
4
5
6
7
8
9
10
11
12
SELECT CASE WHEN LEVEL-1 > 0 then CONCAT(CONCAT(REPEAT('    ', level  - 1),'┗'), ani.nm)
                 ELSE ani.nm
           END AS nm
     , ani.id
     , ani.p_id
     , fnc.level
  FROM
     (SELECT fnc_hierarchi() AS id, @level AS level
        FROM (SELECT @start_with:=0, @id:=@start_with, @level:=0) vars
          JOIN ANIMAL
         WHERE @id IS NOT NULL) fnc
  JOIN ANIMAL ani ON fnc.id = ani.id
cs


의도한 대로 쿼리결과가 나온것을 확인했다.



  1. Chocobe 2019.12.22 02:48

    좋은 정보 감사합니다 ㅎㅎ
    혹시 MySQL 책좀 추천해 주실수 있나요?
    아직 학생이라 어떤 책을 봐야할지 모르겠네요ㅎㅎ;

    • effortDev 2019.12.22 14:54 신고

      안녕하세요. 저는 MySQL 책을 따로 보지 않아서 추천드릴 서적이 없습니다 ㅠㅠ 도움을 드리지 못해 죄송합니다.

  2. 행인 2020.01.08 13:47

    멋진 정리 정말 감사합니다 도움이 되었습니다!!

  3. 테스터 2020.02.12 18:06

    ANIMAL테이블에 인서트할 때 순서를 뒤죽박죽 바꿔서 해보시겠어요?
    제 MYSQL 환경에서는 인서트한 순서대로 계층표현이 되고 인서트 순서를 바꿨을때는 계층표현이 되지 않아요

    • effortDev 2020.02.26 18:49 신고

      네 인서트 한 순서를 바꾸면 당연히 결과가 이상하게 나옵니다. 왜냐하면 데이터가 들어올때마다 primary key(기본키)로 id가 1씩 auto increment 됩니다. animal 테이블을 생성할때 id라는 컬럼에 선언을 해준것이구요. 자신의 id가 달라지면 바라보는 각자의 parent_id가 달라지므로 정상적으로 나오지 않습니다.

  4. ddd 2020.12.21 17:31

    오라클은 START WITH CONNECT BY 로 간단하게 끝나는데 MYSQL은 대체 몇줄인지...ㅋㅋ

    • effortDev 2020.12.27 15:04 신고

      ㅎㅎㅎ부족한 블로그 방문해주셔서 정말 감사드립니다.

  5. 질문이 있습니다. 2020.12.30 00:06

    안녕하세요, 블로그 포스트 잘 봤습니다.

    그런데 제가 질문이 한 개 있는데 혹여 답변주실 수 있으실까요..? 이것때문에 미치겠습니다.

    제가 타 사이트에 써 놓은 글 주소 남겨두겠습니다. (질문이 좀 길어서요..ㅠㅠ)

    혹여 답변 주신다면 대단히 감사하겠습니다.

    https://okky.kr/article/844862

    • 질문이 있습니다. 2020.12.30 00:34

      left outer join 쓰니깐 간단히 해결되는군요.

      저와 같은 초보에게 소중한 블로그 글 감사드립니다.

      코로나 조심하셔요.. :)

    • effortDev 2021.02.14 21:13 신고

      아아 해결되셨다니 다행이네요.
      방문해주셔서 감사합니다.

  6. rioe 2021.06.02 14:20

    혹시 select문 조회시 테이블 결과가 0row로 결과값이 안나오는데,
    해결방법 아시나요?ㅠㅠ

    • effortDev 2021.06.06 14:21 신고

      혹시 제가 포스팅 순서대로 하셨나요?
      id가 오토 인크리먼트 되어서 데이터를 넣을때 포스팅된 순서대로 넣어야합니다. 아니면 ANIMAL의 id의 오토 인크리먼트를 뺴야합니다.

+ Recent posts