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 ( SELECT tt.fid, TRIM(LEADING ‘/‘ FROM SYS_CONNECT_BY_PATH(tt.fid, ‘/‘ )) as fpath from temp tt 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 |