oracle 遞迴查詢

標籤：

實驗代碼

 1 with view_test as ( 2  3      select ‘A‘ AS ID,‘X‘ AS P_ID,‘C‘ AS C_ID,‘1‘ AS IND_EX FROM DUAL 4       5      UNION 6      select ‘C‘ AS ID,‘A‘ AS P_ID,‘B‘ AS C_ID,‘1_1‘ AS IND_EX FROM DUAL 7      UNION 8      select ‘B‘ AS ID,‘C‘ AS P_ID,‘A‘ AS C_ID,‘1_2‘ AS IND_EX FROM DUAL 9      /*UNION10      select ‘A‘ AS ID,‘B‘ AS P_ID,‘C‘ AS C_ID FROM DUAL*/11      UNION12      select ‘A‘ AS ID,‘B‘ AS P_ID,‘X‘ AS C_ID,‘1_3‘ AS IND_EX FROM DUAL13      14      UNION15      select ‘C‘ AS ID,‘A‘ AS P_ID,‘D‘ AS C_ID, ‘2_1‘ AS IND_EX FROM DUAL16      UNION17      select ‘D‘ AS ID,‘C‘ AS P_ID,‘A‘ AS C_ID, ‘2_2‘ AS IND_EX FROM DUAL18      UNION19      select ‘A‘ AS ID,‘D‘ AS P_ID,‘X‘ AS C_ID, ‘2_3‘ AS IND_EX FROM DUAL20      35 )36 37   select 38        sys_connect_by_path(ID,‘>‘) AS ID39        ,sys_connect_by_path(IND_EX,‘>‘) as ind_ex 40   from 41        view_test 42   start 43         with P_ID = ‘X‘ 44   connect by 45           P_ID = prior ID  AND ID = prior C_ID  46                /*AND  ID != prior P_ID */ AND prior C_ID != ‘X‘

實驗結果：

1    >A    >12    >A>C    >1>1_13    >A>C>B    >1>1_1>1_24    >A>C>B>A    >1>1_1>1_2>1_35    >A>C    >1>2_16    >A>C>D    >1>2_1>2_27    >A>C>D>A    >1>2_1>2_2>2_3

 

oracle 遞迴查詢