꼬리에꼬리를 물어 찾아가는 SQL

DROP TABLE KENNYTEST;

 

CREATE TABLE KENNYTEST
(
XPS_ID VARCHAR2(8),
PRE_XPS_ID VARCHAR2(8)
);

 

INSERT INTO KENNYTEST(XPS_ID, PRE_XPS_ID) VALUES('X1', '*');
INSERT INTO KENNYTEST(XPS_ID, PRE_XPS_ID) VALUES('X2', 'X1');
INSERT INTO KENNYTEST(XPS_ID, PRE_XPS_ID) VALUES('X3', 'X2');
INSERT INTO KENNYTEST(XPS_ID, PRE_XPS_ID) VALUES('Y1', '*');
INSERT INTO KENNYTEST(XPS_ID, PRE_XPS_ID) VALUES('Y2', 'Y1');
INSERT INTO KENNYTEST(XPS_ID, PRE_XPS_ID) VALUES('Z1', '*');
INSERT INTO KENNYTEST(XPS_ID, PRE_XPS_ID) VALUES('Z2', 'Z1');
INSERT INTO KENNYTEST(XPS_ID, PRE_XPS_ID) VALUES('Z3', 'Z2');
INSERT INTO KENNYTEST(XPS_ID, PRE_XPS_ID) VALUES('Z4', 'Z3');

 

 

 

 

SELECT  * FROM KENNYTEST;

SELECT  SUBSTR(XPS_ID_CON,- LENGTHB(XPS_ID_CON) + 1) RESULT_VALUE
FROM    (
        SELECT  XPS_ID_CON
               ,XPS_ID
               ,PRE_XPS_ID
               ,RN
               ,LV
               ,DECODE(XPS_ID,LEAD(PRE_XPS_ID) OVER( ORDER BY RN),'F','T') IS_LEAF
        FROM    (
                SELECT  SYS_CONNECT_BY_PATH(XPS_ID,'>') XPS_ID_CON
                       ,XPS_ID
                       ,PRE_XPS_ID
                       ,ROWNUM RN
                       ,LEVEL LV
                FROM    KENNYTEST   A
                CONNECT BY
                PRIOR A.XPS_ID = A.PRE_XPS_ID
                START WITH PRE_XPS_ID = '*'
                ORDER SIBLINGS BY XPS_ID
                )
       )
WHERE   IS_LEAF = 'T'
;

 

------------------

결과

------------------

X1>X2>X3
Y1>Y2
Z1>Z2>Z3>Z4