MySQL 계층형 재귀쿼리 - Recursive 사용

MySQL 재귀쿼리

    WITH RECURSIVE CTE AS 

    (

    SELECT MENU_ID

         , MENU_NM

         , PARENT_ID

      FROM MDPJ_MENU 

     WHERE MENU_ID = '1'

    UNION ALL

    SELECT A.MENU_ID

         , A.MENU_NM

         , A.PARENT_ID

      FROM MDPJ_MENU A

     INNER JOIN CTE B ON A.PARENT_ID = B.MENU_ID 

    )

    SELECT MENU_ID , MENU_NM  , PARENT_ID  FROM CTE

Table

MENU_ID	int(11)	NO	PRI		auto_increment
MENU_NM	varchar(50)	YES
MENU_LVL	varchar(50)	YES
PARENT_ID	varchar(50)	YES
ORDER_BY	varchar(300)	YES
CREATED_BY	varchar(50)	NO
CREATED_DATE	datetime	YES
UPDATED_BY	varchar(50)	YES
UPDATED_DATE	datetime	YES

CREATE TABLE MDPJ_MENU

(

    `MENU_ID`       INT             NOT NULL    AUTO_INCREMENT COMMENT '메뉴 아이디', 

    `MENU_NM`       VARCHAR(50)     NULL        COMMENT '메뉴 이름', 

    `MENU_LVL`      VARCHAR(50)     NULL        COMMENT '메뉴 레벨', 

    `PARENT_ID`     VARCHAR(50)     NULL        COMMENT '부모메뉴 아이디', 

    `ORDER_BY`      VARCHAR(300)    NULL        COMMENT '정렬순서', 

    `CREATED_BY`    VARCHAR(50)     NOT NULL    COMMENT '최초생성자', 

    `CREATED_DATE`  DATETIME        NULL        COMMENT '최초생성일', 

    `UPDATED_BY`    VARCHAR(50)     NULL        COMMENT '마지막수정자', 

    `UPDATED_DATE`  DATETIME        NULL        COMMENT '마지막수정일', 

    PRIMARY KEY (MENU_ID)

);

ALTER TABLE MDPJ_MENU COMMENT '메뉴';

Data

1	프로젝트	1		1	CREATED_BY 01	2018-08-13 21:42:11	UPDATED_BY 01	2018-08-13 21:42:11
2	프로젝트 센터	2	1	2	CREATED_BY 02	2018-08-13 21:42:11	UPDATED_BY 02	2018-08-13 21:42:11
3	프로젝트 산출물	2	1	3	CREATED_BY 03	2018-08-13 21:42:11	UPDATED_BY 03	2018-08-13 21:42:11
4	프로젝트 작업승인	2	1	4	CREATED_BY 04	2018-08-13 21:42:11	UPDATED_BY 04	2018-08-13 21:42:11
5	Gate Review	2	1	5	CREATED_BY 05	2018-08-13 21:42:11	UPDATED_BY 05	2018-08-13 21:42:11
6	Gate Review 승인	2	1	6	CREATED_BY 06	2018-08-13 21:42:11	UPDATED_BY 06	2018-08-13 21:42:11
7	내 작업	1		1	CREATED_BY 07	2018-08-13 21:42:11	UPDATED_BY 07	2018-08-13 21:42:11
8	내 작업	2	7	2	CREATED_BY 08	2018-08-13 21:42:11	UPDATED_BY 08	2018-08-13 21:42:11
9	일반 작업	2	7	3	CREATED_BY 09	2018-08-13 21:42:11	UPDATED_BY 09	2018-08-13 21:42:11
10	일정관리	2	7	4	CREATED_BY 10	2018-08-13 21:42:11	UPDATED_BY 10	2018-08-13 21:42:11
11	일반업무	1		1	CREATED_BY 11	2018-08-13 21:42:11	UPDATED_BY 11	2018-08-13 21:42:11
12	이슈관리	2	11	2	CREATED_BY 12	2018-08-13 21:42:11	UPDATED_BY 12	2018-08-13 21:42:11
13	변경요청	2	11	3	CREATED_BY 13	2018-08-13 21:42:11	UPDATED_BY 13	2018-08-13 21:42:11
14	커뮤니티	1		1	CREATED_BY 14	2018-08-13 21:42:11	UPDATED_BY 14	2018-08-13 21:42:11
15	포럼	2	14	2	CREATED_BY 15	2018-08-13 21:42:11	UPDATED_BY 15	2018-08-13 21:42:11
16	팀챗	2	14	3	CREATED_BY 16	2018-08-13 21:42:11	UPDATED_BY 16	2018-08-13 21:42:11
17	사용자현황	1		1	CREATED_BY 17	2018-08-13 21:42:11	UPDATED_BY 17	2018-08-13 21:42:11
18	사용자리스트	2	17	2	CREATED_BY 18	2018-08-13 21:42:11	UPDATED_BY 18	2018-08-13 21:42:11
19	사용자 배정현황	2	17	3	CREATED_BY 19	2018-08-13 21:42:11	UPDATED_BY 19	2018-08-13 21:42:11