生成分層路徑

September 21, 2019

我有一張classstructure桌子：

create table classstructure (classstructureid number(8,0), classificationid varchar2(25), parent number(8,0));

insert into classstructure(classstructureid, classificationid, parent) values(1001, 'FLEET', null);
insert into classstructure(classstructureid, classificationid, parent) values(1002, 'LIGHTDUTYVEHICLE', 1001);
insert into classstructure(classstructureid, classificationid, parent) values(1004, 'MEDIUMDUTYVEHICLE', 1001);
insert into classstructure(classstructureid, classificationid, parent) values(1022, 'ACTIVETRANSPORTATION', null);
insert into classstructure(classstructureid, classificationid, parent) values(1023, 'FACILITYWALKWAY', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1024, 'TRAIL', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1085, 'SIDEWALK', 1022);
insert into classstructure(classstructureid, classificationid, parent) values(1091, 'SDWRAMP', 1085);
commit;

select * from classstructure;

CLASSSTRUCTUREID CLASSIFICATIONID              PARENT
---------------- ------------------------- ----------
           1001 FLEET                               
           1002 LIGHTDUTYVEHICLE                1001
           1004 MEDIUMDUTYVEHICLE               1001

           1022 ACTIVETRANSPORTATION                
           1023 FACILITYWALKWAY                 1022
           1024 TRAIL                           1022
           1085 SIDEWALK                        1022
           1091 SDWRAMP                         1085

我想將記錄折疊到層次結構路徑：

HIERARCHYPATH
---------------------------
FLEET \ LIGHTDUTYVEHICLE
FLEET \ MEDIUMDUTYVEHICLE 

ACTIVETRANSPORTATION \ FACILITYWALKWAY
ACTIVETRANSPORTATION \ TRAIL
ACTIVETRANSPORTATION \ SIDEWALK
ACTIVETRANSPORTATION \ SIDEWALK \ SDWRAMP

我怎樣才能做到這一點？

看看這是否有幫助。

SQL&gt; select ltrim(sys_connect_by_path(classificationid, ' \ '), ' \ ') hierarchypath
 2  from classstructure
 3  where parent is not null
 4  connect by prior classstructureid = parent
 5  start with parent is null;

HIERARCHYPATH
--------------------------------------------------------------------------------
FLEET \ LIGHTDUTYVEHICLE
FLEET \ MEDIUMDUTYVEHICLE
ACTIVETRANSPORTATION \ FACILITYWALKWAY
ACTIVETRANSPORTATION \ TRAIL
ACTIVETRANSPORTATION \ SIDEWALK
ACTIVETRANSPORTATION \ SIDEWALK \ SDWRAMP

6 rows selected.

SQL&gt;