MySQL 계층형 쿼리 구현하기
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'
오라클 쿼리 결과
MySQL에서 위와 같은 계층형 쿼리로 구현하고자 한다면 Oracle에서 제공하는 구문을 사용할 수 없다.
아래는 테스트로 구현할 계층형 형태의 쿼리 결과이다.
동물
┗말
┗얼룩말
┗조랑말
┗망아지
┗닭
┗흰닭
┗흰병아리
┗흰달걀
┗검은닭
┗검은병아리
┗검은달걀
MySQL에서 계층형 쿼리 구문이 따로 없어
함수를 만들어 재귀함수를 사용해 구현해본다.
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
ANIMAL 테이블에 데이터를 넣어준다.
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, '검은달걀');
함수 fnc_hierarchi 를 만들어준다.
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
의도한 대로 쿼리결과가 나온것을 확인했다.
'Database > mysql tip' 카테고리의 다른 글
MySQL Connection Memory Management (0) | 2021.04.11 |
---|---|
mysql Index 와 다중 컬럼 인덱스 (0) | 2021.04.11 |
mysqldump 사용법 (0) | 2021.04.11 |
select return (0 row 0 total rows) null 결과 출력처리방법 (0) | 2021.04.06 |
SQL EXPLAIN 정리 (0) | 2021.04.06 |
[MySQL][TIP] MySQL JOIN(LEFT, RIGHT, INNER, OUTER) (0) | 2021.01.06 |
[MySQL][TIP] 필드 원소 갯수, 각각 원소 갯수세기 SUM IF (0) | 2021.01.06 |
[MySQL][TIP] DB 스키마 정보 쿼리(Query). 스키마 엑셀로 문서화 하기 (0) | 2021.01.06 |