connect by prior start with 구분 MySQL 계층형 쿼리 구현 > 데이터베이스

본문 바로가기
사이트 내 전체검색


회원로그인

데이터베이스

MySQL | connect by prior start with 구분 MySQL 계층형 쿼리 구현

페이지 정보

작성자 100K5 작성일19-08-30 11:41 조회46,002회 댓글0건

본문

Oracle에서는 CONNECT BY PRIOR, START WITH를 사용하여
자신의 ID와 연결된 부모 ID를 찾아가 계층적으로 쿼리결과를 뽑을 수 있다.

 

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

 

-- 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'

 

오라클 쿼리 결과

99B1713E5D2D60551A

 

MySQL에서 위와 같은 계층형 쿼리로 구현하고자 한다면 Oracle에서 제공하는 구문을 사용할 수 없다. 따라서 재귀함수를 생성해서 구현을 해야 된다.

 

임의의 메뉴 테이블(ANIMAL)을 생성 쿼리

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

CREATE TABLE `ANIMAL` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`p_id` int(10) unsigned default '0',
`nm` varchar(50),
primary key(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

 

임의의 데이터을 추가 한다.

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

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, '검은달걀');

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

 

함수 생성 쿼리

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

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 ;

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

 

테이블을 조회한다.

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

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

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

 

결과
99ABC0435D2D5D8B0D 

원글 : https://shlee0882.tistory.com/241

 

 

댓글목록

등록된 댓글이 없습니다.


사이트소개 개인정보취급방침 서비스이용약관 Copyright © kkujunhee.net All rights reserved.
상단으로

개인정보관리책임자 : 관리자

모바일 버전으로 보기