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
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。