[Oracle] 계층형 원 조상 가져오기(CONNECT_BY_ROOT)

[Oracle] 계층형 원 조상 가져오기(CONNECT_BY_ROOT)

WITH TEMP AS ( SELECT '0' Seq, NULL P_Level, 'A-1' C_Level FROM DUAL UNION ALL SELECT '1' Seq,'A-1' P_Level, 'A-2' C_Level FROM DUAL UNION ALL SELECT '2' Seq,'A-1' P_Level, 'A-3' C_Level FROM DUAL UNION ALL SELECT '3' Seq,'A-2' P_Level, 'A-4' C_Level FROM DUAL UNION ALL SELECT '4' Seq,'A-4' P_Level, 'A-5' C_Level FROM DUAL UNION ALL SELECT '5' Seq,'A-5' P_Level, 'A-6' C_Level FROM DUAL UNION ALL SELECT '6' Seq,NULL P_Level, 'B-1' C_Level FROM DUAL UNION ALL SELECT '7' Seq,'B-1' P_Level, 'B-2' C_Level FROM DUAL UNION ALL SELECT '8' Seq,'B-1' P_Level, 'B-3' C_Level FROM DUAL UNION ALL SELECT '9' Seq,'B-2' P_Level, 'B-4' C_Level FROM DUAL UNION ALL SELECT '10' Seq,'B-4' P_Level, 'B-5' C_Level FROM DUAL UNION ALL SELECT '11' Seq,'B-5' P_Level, 'B-6' C_Level FROM DUAL ) SELECT Seq,P_Level,C_Level,LEVEL, CONNECT_BY_ROOT C_Level FROM TEMP start with (P_Level is null ) CONNECT BY PRIOR C_Level = P_Level