with tb_data as ( select null cod,null nam,null typ,null ord from dual where 1=2 union all select '00' cod,'集团总公司' nam,'00' typ,1 ord from dual union all select '0002' cod,'部门1' nam,'01' typ,1 ord from dual union all select '0003' cod,'部门1' nam,'01' typ,2 ord from dual union all select '0001' cod,'分公司1' nam,'00' typ,3 ord from dual union all select '000101' cod,'部门1-1' nam,'01' typ,1 ord from dual union all select '00010101' cod,'部门1-1-1' nam,'01' typ,1 ord from dual union all select '00010102' cod,'部门1-1-2' nam,'01' typ,2 ord from dual union all select '000102' cod,'部门1-2' nam,'01' typ,2 ord from dual union all select '0004' cod,'分公司2' nam,'00' typ,4 ord from dual union all select '000401' cod,'部门2-1' nam,'01' typ,1 ord from dual) Select t.*, -- Level, --递归层次 sys_connect_by_path(cod, ',') path --路径 From tb_data t Start With cod = '00' --起始条件 Connect By cod Like Prior cod || '__' --指定父子关系 Order Siblings By ord --兄弟间排序 ;
select null cod,null nam,null typ,null ord from dual where 1=2 union all
select '00' cod,'集团总公司' nam,'00' typ,1 ord from dual union all
select '0002' cod,'部门1' nam,'01' typ,1 ord from dual union all
select '0003' cod,'部门1' nam,'01' typ,2 ord from dual union all
select '0001' cod,'分公司1' nam,'00' typ,3 ord from dual union all
select '000101' cod,'部门1-1' nam,'01' typ,1 ord from dual union all
select '00010101' cod,'部门1-1-1' nam,'01' typ,1 ord from dual union all
select '00010102' cod,'部门1-1-2' nam,'01' typ,2 ord from dual union all
select '000102' cod,'部门1-2' nam,'01' typ,2 ord from dual union all
select '0004' cod,'分公司2' nam,'00' typ,4 ord from dual union all
select '000401' cod,'部门2-1' nam,'01' typ,1 ord from dual)
Select t.*, --
Level, --递归层次
sys_connect_by_path(cod, ',') path --路径
From tb_data t
Start With cod = '00' --起始条件
Connect By cod Like Prior cod || '__' --指定父子关系
Order Siblings By ord --兄弟间排序
;
还需要多学习oracle函数了,有好多都没有接触到。