공부자료/MySQL2018. 8. 13. 22:37

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)NOPRIauto_increment
MENU_NMvarchar(50)YES
MENU_LVLvarchar(50)YES
PARENT_IDvarchar(50)YES
ORDER_BYvarchar(300)YES
CREATED_BYvarchar(50)NO
CREATED_DATEdatetimeYES
UPDATED_BYvarchar(50)YES
UPDATED_DATEdatetimeYES


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프로젝트11CREATED_BY 012018-08-13 21:42:11UPDATED_BY 012018-08-13 21:42:11
2프로젝트 센터212CREATED_BY 022018-08-13 21:42:11UPDATED_BY 022018-08-13 21:42:11
3프로젝트 산출물213CREATED_BY 032018-08-13 21:42:11UPDATED_BY 032018-08-13 21:42:11
4프로젝트 작업승인214CREATED_BY 042018-08-13 21:42:11UPDATED_BY 042018-08-13 21:42:11
5Gate Review215CREATED_BY 052018-08-13 21:42:11UPDATED_BY 052018-08-13 21:42:11
6Gate Review 승인216CREATED_BY 062018-08-13 21:42:11UPDATED_BY 062018-08-13 21:42:11
7내 작업11CREATED_BY 072018-08-13 21:42:11UPDATED_BY 072018-08-13 21:42:11
8내 작업272CREATED_BY 082018-08-13 21:42:11UPDATED_BY 082018-08-13 21:42:11
9일반 작업273CREATED_BY 092018-08-13 21:42:11UPDATED_BY 092018-08-13 21:42:11
10일정관리274CREATED_BY 102018-08-13 21:42:11UPDATED_BY 102018-08-13 21:42:11
11일반업무11CREATED_BY 112018-08-13 21:42:11UPDATED_BY 112018-08-13 21:42:11
12이슈관리2112CREATED_BY 122018-08-13 21:42:11UPDATED_BY 122018-08-13 21:42:11
13변경요청2113CREATED_BY 132018-08-13 21:42:11UPDATED_BY 132018-08-13 21:42:11
14커뮤니티11CREATED_BY 142018-08-13 21:42:11UPDATED_BY 142018-08-13 21:42:11
15포럼2142CREATED_BY 152018-08-13 21:42:11UPDATED_BY 152018-08-13 21:42:11
16팀챗2143CREATED_BY 162018-08-13 21:42:11UPDATED_BY 162018-08-13 21:42:11
17사용자현황11CREATED_BY 172018-08-13 21:42:11UPDATED_BY 172018-08-13 21:42:11
18사용자리스트2172CREATED_BY 182018-08-13 21:42:11UPDATED_BY 182018-08-13 21:42:11
19사용자 배정현황2173CREATED_BY 192018-08-13 21:42:11UPDATED_BY 192018-08-13 21:42:11

'공부자료 > MySQL' 카테고리의 다른 글

MySQL 계층형 재귀쿼리 - Recursive 사용  (0) 2018.08.13
포스팅이 유익하셨다면 추천 부탁드립니다. ^^*
한RSS로 구독하기 iGoogle or 구글리더로 구독하기 다음뷰로 구독하기 믹시로 구독하기 블로그코리아로 구독하기 Revu 트위터
Posted by MIS MIS경영정보

댓글을 달아 주세요