如何形成一个递归路径字段？我需要一个视图

[全粘架构师]

例如有一张表 id 和 fid 父子关联
id fid title
1 0 root
2 1 a
3 1 b
4 2 c
5 2 d
6 3 e
7 3 f
8 6 g

我需要一个视图
id fid title path
1 0 root root
2 1 a root-a
3 1 b root-b
4 2 c root-a-c
5 2 d root-a-d
6 3 e root-b-e
7 3 f root-b-f
8 6 g root-b-e-g

请问如何实现？

[小兽]

使用connect by

[HelloWorld_001]

我记得以前有这样的帖子的，我的方法不好，数据量大就死定了

with A as(
select 1 as id,0 as fid,'root' as title from dual union
select 2 as id,1 as fid,'a' as title from dual union
select 3 as id,1 as fid,'b' as title from dual union
select 4 as id,2 as fid,'c' as title from dual union
select 5 as id,2 as fid,'d' as title from dual union
select 6 as id,3 as fid,'e' as title from dual union
select 7 as id,3 as fid,'f' as title from dual union
select 8 as id,6 as fid,'g' as title from dual
)

select id,fid,MAX(SYS_CONNECT_BY_PATH(title,'-')) title FROM
(SELECT id,fid,title,Level As rn FROM A Start With fid=0 Connect By Prior Id=fid)
START WITH RN=1
CONNECT BY RN-1=PRIOR RN
Group By id,fid

[小兽]

设表名为 test_gz_a_01 SQL如下：

select id, fid, title, substr(path, 2) path from
(
select id, fid, title, sys_connect_by_path(title, '-') path from test_gz_a_01
start with id in (select id from test_gz_a_01)
connect by fid = prior id
)
where path like '%root%'
order by id
;

已经测试过，没有问题。

[utpcb]

你用循环做嘛　不要游标！　数据量大都没有问题的　

[doer_ljy]

3楼正解，使用sys_connect_by_Path函数，表示楚楚的路径结构即可。

[全粘架构师]

谢谢了