oracle 递归查询

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 /*UNION 10 select ‘A‘ AS ID,‘B‘ AS P_ID,‘C‘ AS C_ID FROM DUAL*/ 11 UNION 12 select ‘A‘ AS ID,‘B‘ AS P_ID,‘X‘ AS C_ID,‘1_3‘ AS IND_EX FROM DUAL 13 14 UNION 15 select ‘C‘ AS ID,‘A‘ AS P_ID,‘D‘ AS C_ID, ‘2_1‘ AS IND_EX FROM DUAL 16 UNION 17 select ‘D‘ AS ID,‘C‘ AS P_ID,‘A‘ AS C_ID, ‘2_2‘ AS IND_EX FROM DUAL 18 UNION 19 select ‘A‘ AS ID,‘D‘ AS P_ID,‘X‘ AS C_ID, ‘2_3‘ AS IND_EX FROM DUAL 20 35 ) 36 37 select 38 sys_connect_by_path(ID,‘>‘) AS ID 39 ,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    >1
2    >A>C    >1>1_1
3    >A>C>B    >1>1_1>1_2
4    >A>C>B>A    >1>1_1>1_2>1_3
5    >A>C    >1>2_1
6    >A>C>D    >1>2_1>2_2
7    >A>C>D>A    >1>2_1>2_2>2_3

 

oracle 递归查询

标签：