oracle生成path的sql语句

oracle生成path的sql语句
UPDATE "table" t1
SET t1. PATH = (
SELECT
'/-1' || SYS_CONNECT_BY_PATH (id, '/') PATH
FROM
"table" t2
WHERE
t2.id= t1.id

START WITH pid= '-1' CONNECT BY PRIOR id= pid

);

 

生成path

select id, pid, '/-1' || sys_connect_by_path(id, '/') path
from "table"
START WITH pid='-1'
CONNECT BY PRIOR id= pid

 

 

另一种思路，用with as建临时表， 用listagg within group over做分组排序

with temp(id_surgca, id_par, flag, level1) as(
select id_surgca, id_par, id_surgca flag, 0 level1 from "HI_BD_SURGCA_copy"
union ALL
select c.id_surgca, c.id_par, t.flag, t.level1 + 1 from "HI_BD_SURGCA_copy" c, temp t where c.id_surgca=t.id_par
)
select flag, '/-1/' || listagg(id_surgca, '/') WITHIN GROUP (ORDER BY level1 desc) paths
from temp
group by flag