with t as (select 1 pid, 1 id, 1 name from dual union all select 1 pid, 2 id, 2 name from dual union all select 1 pid, 3 id, 3 name from dual union all select 1 pid, 4 id, 4 name from dual union all select 2 pid, 5 id, 5 name from dual) select r_nm, listagg(name, ',') within GROUP(ORDER BY rn) from (select name, rn, CONNECT_BY_ROOT(NAME) r_nm from (select t.*, rownum rn from t) t start with id = 1 connect by prior id = pid and id <> 1 order by rownum) group by r_nm;
,WM_CONCAT(ID_) ids
FROM SYS_BUSINESS_A START WITH ID_ = 1
CONNECT BY nocycle PRIOR ID_ = PID_
CONNECT BY PRIOR ID_ = PID_ START WITH ID_ = 1换换顺序 行吗?
(select 1 pid, 1 id, 1 name
from dual
union all
select 1 pid, 2 id, 2 name
from dual
union all
select 1 pid, 3 id, 3 name
from dual
union all
select 1 pid, 4 id, 4 name
from dual
union all
select 2 pid, 5 id, 5 name
from dual)
select r_nm, listagg(name, ',') within GROUP(ORDER BY rn)
from (select name, rn, CONNECT_BY_ROOT(NAME) r_nm
from (select t.*, rownum rn from t) t
start with id = 1
connect by prior id = pid
and id <> 1
order by rownum)
group by r_nm;