create table COMPANY_INFO
(
GUID VARCHAR2(45) not null,
P_COMPANY_ID VARCHAR2(45),
COMPANY_NAME VARCHAR2(50) not null,
COMPANY_CODE VARCHAR2(3),
)
查询A下面的所有结点
select level ,sys_connect_by_path( t.guid , '/' ) tree,sys_connect_by_path( t.company_name , '/' ) nn
from company_info t
connect by prior t.guid= t.p_company_id
start with t.guid ='A'
sys_connect_by_path 函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分,然后连接在一个列中显示
假如一个树形表,要加上一个分层字段level_,更新原表语句:
update e_incidentclass tt set tt.classlevel=(
select le from (
select level le,tt.* from e_incidentclass tt
connect by prior tt.id_incidentclass =tt.parent_id
start with tt.parent_id= -1
) se where se.id_incidentclass=tt.id_incidentclass
)