SELECT 主 FROM AA start with 主='A' connect by prior 元 = 主
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as SQL> SQL> with table1 as 2 ( 3 select 'A' parent1,'B' child1 from dual 4 union all 5 select 'B' ,'C' from dual 6 union all 7 select 'C','D' from dual 8 union all 9 select 'K','E' from dual 10 ) 11 select concat('A->', replace(wm_concat(child1), ',', '->')) 12 from table1 13 start with parent1 = 'A' 14 connect by prior child1 = parent1 15 /CONCAT('A->',REPLACE(WM_CONCAT -------------------------------------------------------------------------------- A->B->C->DSQL>
10:01:52 tina@PRACTICE> with tb as ( 10:01:54 2 select 'A' 主,'B' 元 from dual 10:01:54 3 union all 10:01:54 4 select 'B','C' from dual 10:01:54 5 union all 10:01:54 6 select 'C','D' from dual 10:01:54 7 ) 10:01:54 8 select 'A->'||replace(wm_concat(元),',','->') result from tb connect by 主 = prior 元 start with 主 = 'A';RESULT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- A->B->C->D已用时间: 00: 00: 00.01
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(T.主, '->')), '->') 主 FROM 結構表 T START WITH T.主 = ? CONNECT BY T.主 = PRIOR T.元 GROUP BY T.主
start with parent1 = 'A' connect by prior child1 = parent1
start with 主='A'
connect by prior 元 = 主
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as
SQL>
SQL> with table1 as
2 (
3 select 'A' parent1,'B' child1 from dual
4 union all
5 select 'B' ,'C' from dual
6 union all
7 select 'C','D' from dual
8 union all
9 select 'K','E' from dual
10 )
11 select concat('A->', replace(wm_concat(child1), ',', '->'))
12 from table1
13 start with parent1 = 'A'
14 connect by prior child1 = parent1
15 /CONCAT('A->',REPLACE(WM_CONCAT
--------------------------------------------------------------------------------
A->B->C->DSQL>
10:01:54 2 select 'A' 主,'B' 元 from dual
10:01:54 3 union all
10:01:54 4 select 'B','C' from dual
10:01:54 5 union all
10:01:54 6 select 'C','D' from dual
10:01:54 7 )
10:01:54 8 select 'A->'||replace(wm_concat(元),',','->') result from tb connect by 主 = prior 元 start with 主 = 'A';RESULT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A->B->C->D已用时间: 00: 00: 00.01
LTRIM(MAX(SYS_CONNECT_BY_PATH(T.主, '->')), '->') 主
FROM 結構表 T
START WITH T.主 = ?
CONNECT BY T.主 = PRIOR T.元
GROUP BY T.主
connect by prior child1 = parent1