select a.*,level from t_customer a
start with customer_id = 'AABB10000010'
connect by prior customer_id?= parent_cust_id
select a.*, level
from t_industry a
start with industry_id? = 'B03'
connect by prior industry_id = parent_id
--connect String
select Substr('bbbcbdb',1,2) ||replace(Substr('bbbcbdb',3,4),'b','a') ||Substr('bbbcbdb',7,1) from dual
?select Replace(customer_name, '->','') as all_customer_name,rownum from (
?select sys_connect_by_path(customer_name, '->') customer_name from (
? select a.customer_name, (level - 1) ll,level l
? from t_customer a
? start with customer_id = 'SH02110000893' connect by prior parent_cust_id=customer_id order by l desc
? )
? connect by prior ll= l order by level desc
? ) c where rownum <2?
根據字節點查出全名
with temp as(
select a.*, (level - 1) ll, level l
? from t_content_type a
?start with a.type_id = '20000000138418'
connect by prior a.parent_type_id = a.type_id
?order by l desc
)
select sys_connect_by_path(temp.type_name, '/') path
? from temp
?start with temp.parent_type_id = '0'
connect by prior temp.type_id = temp.parent_type_id