例如有一张表 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请问如何实现?
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请问如何实现?
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
(
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
;已经测试过,没有问题。