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(10) unsigned NOT NULL AUTO_INCREMENT, `p_id` int(10) unsigned 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 |
의도한 대로 쿼리결과가 나온것을 확인했다.
'전체 > MySQL' 카테고리의 다른 글
| select return (0 row 0 total rows) null 결과 출력처리방법 (0) | 2019.07.19 |
|---|---|
| MySQL function 만들기 (3) | 2019.07.17 |
| SQL EXPLAIN 정리 (0) | 2018.11.19 |
| ANSI Syntax and Conventional syntax(non-ANSI) 안시조인 사용 이유 (0) | 2018.10.29 |
| group by 이해하기 (0) | 2018.10.24 |
