oracle 同一张表 数据是树形结构
fid(主键) parentid(父ID) fpath(节点全路径)
1 1
2 1 1/2
3 1 1/3
4 2 1/2/4
但是现有数据是用excel导入的 fpath 是空值,现在想写条语句把 fpath全都加上,求语句
建表及数百据
1
2
3
4
5
6
7
8
9
10
11
|
create
table
test
(fid
int
,
parentid
int
,
fpath varchar2(100));
insert
into
test
values
(1,
null
,
null
);
insert
into
test
values
(2,1,
null
);
insert
into
test
values
(3,1,
null
);
insert
into
test
values
(4,2,
null
);
|
执行更度新版语句
1
2
3
4
5
6
7
8
|
update
test a
set
a.fpath=
(
select
b.fpath
from
(
select
fid,parentid,
substr(sys_connect_by_path(fid,
'/'
),2) fpath
from
test
start
with
fid=1
connect
by
prior
fid=parentid) b
where
a.fid=b.fid);
|
效果截图权
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
CREATE
TABLE
temp
(
fid
int
,
parentid
int
,
fpath
varchar
(20)
);
insert
into
temp
(fid, parentid)
values
(1,
null
);
insert
into
temp
(fid, parentid)
values
(2, 1);
insert
into
temp
(fid, parentid)
values
(3, 1);
insert
into
temp
(fid, parentid)
values
(4, 2);
update
temp
set
fpath = (
SELECT
fpath
from
(
START
WITH
tt.parentid
is
null
CONNECT
BY
PRIOR
tt.fid = tt.parentid
) t
where
t.fid =
temp
.fid
);
已更新e68a84e8a2ad7a64313333326432354行。
SQL>
select
*
from
temp
;
FID PARENTID FPATH
---------- ---------- ----------------------------------------
1 1
2 1 1/2
3 1 1/3
4 2 1/2/4
|