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가 달라지므로 정상적으로 나오지 않습니다.

+ Recent posts