계층형쿼리

WITH RECURSIVE TMP_TABLE AS

(

    SELECT

       SQ,

       GROUP_NAME,

       PARENT_SQ,

       CAST(GROUP_NAME AS CHAR(200)) PATH,

       END_YN,

       ENABLE_YN,

       UNIT_ID,

       1 AS LVL

    FROM T_TICKET_GROUP WHERE PARENT_SQ = 0

    UNION ALL

    SELECT

       TG.SQ,

       TG.GROUP_NAME,

       TG.PARENT_SQ,

       CONCAT(TMP.PATH,'->',TG.GROUP_NAME) AS PATH,

       TG.END_YN,

       TG.ENABLE_YN,

       TG.UNIT_ID,

       TMP.LVL+1 AS LVL

    FROM TMP_TABLE TMP JOIN T_TICKET_GROUP TG ON (TMP.SQ = TG.PARENT_SQ AND TMP.UNIT_ID = TG.UNIT_ID)

    AND TG.UNIT_ID = 1

    AND TG.ENABLE_YN = 'Y'   

)

SELECT SQ, GROUP_NAME, PARENT_SQ, PATH, END_YN, ENABLE_YN, UNIT_ID, LVL

FROM TMP_TABLE

ORDER BY PATH

;

 

WITH RECURSIVE TMP_TABLE AS

(

    SELECT

       SQ,

       GROUP_NAME,

       PARENT_SQ,

       CAST(GROUP_NAME AS CHAR(200)) PATH,

       END_YN,

       ENABLE_YN,

       UNIT_ID,

       GOODS_TYPE,

       1 AS LVL

    FROM T_GOODS_GROUP WHERE PARENT_SQ = 0

    UNION ALL

    SELECT

       TG.SQ,

       TG.GROUP_NAME,

       TG.PARENT_SQ,

       CONCAT(TMP.PATH,'->',TG.GROUP_NAME) AS PATH,

       TG.END_YN,

       TG.ENABLE_YN,

       TG.UNIT_ID,

       TG.GOODS_TYPE,

       TMP.LVL+1 AS LVL

    FROM TMP_TABLE TMP JOIN T_GOODS_GROUP TG ON (TMP.SQ = TG.PARENT_SQ AND TMP.UNIT_ID = TG.UNIT_ID)

    AND TG.UNIT_ID = 1

    AND TG.ENABLE_YN = 'Y'   

)

SELECT SQ, GROUP_NAME, PARENT_SQ, PATH, END_YN, ENABLE_YN, UNIT_ID, GOODS_TYPE,LVL

FROM TMP_TABLE

ORDER BY PATH

;

 

WHERE TICKET_TYPE <![CDATA[<>]]> 3