oracle-层次化查询

1、层次化查询
层次化：同一数据库表中多条数据存在父子关系，形成树状结构。

利用connect by 进行层次化查询
按照某种规则，获得节点路径上的节点集合。

如：
select * from market
[b]start with[/b] m_id = 10
[b]connect by prior[/b] p_id = m_id;
查询出m_id为14的记录其所有的父级记录。
start with m_id = 10:从m_id 为10的记录开始查询

根据子节点获取顶层父节点
select it.industry_type_cd
from industry_type it
where it.parent_industry_type_cd = '0'
start with it.industry_type_cd =
pa.investment_industry
connect by prior it.parent_industry_type_cd =
it.industry_type_cd


select *
from ec_org_department ec
where ec.levels = '2'
and instr(ec.name,'专项') >0
--and ec.name like '%专项%';
start
with ec.id != '10001'
connect by prior ec.id = ec.parentdepartmentid