안녕하세요 아래 테이블로 결과를 산출하는 쿼리 부탁드립니다
<테이블>
fr_act to_act
a b
b c
c d
ㄱ ㄴ
ㄴ ㄷ
<결과>
a c
a d
ㄱ ㄷ
감사합니다
WITH t AS ( SELECT 'a' fr_act, 'b' to_act FROM dual UNION ALL SELECT 'b', 'c' FROM dual UNION ALL SELECT 'c', 'd' FROM dual UNION ALL SELECT 'ㄱ', 'ㄴ' FROM dual UNION ALL SELECT 'ㄴ', 'ㄷ' FROM dual ) SELECT fr_act , CONNECT_BY_ROOT(to_act) to_act FROM t WHERE CONNECT_BY_ISLEAF = 1 CONNECT BY PRIOR fr_act = to_act ;
SELECT CONNECT_BY_ROOT(fr_act) fr_act , to_act FROM t a START WITH NOT EXISTS (SELECT 1 FROM t WHERE to_act = a.fr_act) CONNECT BY PRIOR to_act = fr_act ;