我现在有一张表 test
有三个字段分别是id(编号)、fid(id的父节点编号)、name(名称)
数据结果如下:
id fid name
1 0 A
2 1 B
3 2 C
4 3 D
以上数据结构其实是一个树型数据结构,fid=0为根节点。
A
B
C
D
先要求写一个试图,将以上四条记录,合并成一个记录 ,如下
id name
4 A\B\C\D
有三个字段分别是id(编号)、fid(id的父节点编号)、name(名称)
数据结果如下:
id fid name
1 0 A
2 1 B
3 2 C
4 3 D
以上数据结构其实是一个树型数据结构,fid=0为根节点。
A
B
C
D
先要求写一个试图,将以上四条记录,合并成一个记录 ,如下
id name
4 A\B\C\D
ID FID NAME
--- --- ----------
1 0 A
2 1 B
3 2 C
4 3 D
SQL> SELECT ID, SYS_CONNECT_BY_PATH(NAME,'\') FROM T1 WHERE CONNECT_BY_ISLEAF=1 START WITH FID=0 CONNECT BY PRIOR ID=FID;
ID SYS_CONNECT_BY_PATH(NAME,'\')
--- --------------------------------------------------------------------------------
4 \A\B\C\D
SQL> CREATE VIEW V_T1 AS SELECT ID, SYS_CONNECT_BY_PATH(NAME,'\') NAME FROM T1 WHERE CONNECT_BY_ISLEAF=1 START WITH FID=0 CONNECT BY PRIOR ID=FID;
View created
SQL> SELECT * FROM V_T1;
ID NAME
--- --------------------------------------------------------------------------------
4 \A\B\C\D
SQL>
select ltrim(sys_connect_by_path(name,'\'),'\')
from test
where connect_by_isleaf=1
start with fid=0
connect by prior id=fid;
(
select 1 id, 0 fid,'A' name from dual
union all
select 2 id, 1 fid,'B' name from dual
union all
select 3 id, 2 fid,'C' name from dual
union all
select 4 id, 3 fid,'D' name from dual
)
select max(ltrim(SYS_CONNECT_BY_PATH(name,'\'),'\') )
from t1
connect by fid = prior id
start with fid =0