有一个表结构:
PARENT CHILD
A B
B C
B D
D E
C F
M B
M L
M N
L Z
Z N
N S怎样可以得到A\Z\M的叶子节点?
结果如:
ROOT LEAF
A F
A E
Z S
M F
M E
M S
谢谢~~~
PARENT CHILD
A B
B C
B D
D E
C F
M B
M L
M N
L Z
Z N
N S怎样可以得到A\Z\M的叶子节点?
结果如:
ROOT LEAF
A F
A E
Z S
M F
M E
M S
谢谢~~~
from hi_org_info
connect by prior orgcode = parentorgcode
start with orgcode = '43'
lz可写存储过程解决问题
with t as (
select 'A' parent1, 'B' child1 from dual union all
select 'B' , 'C' from dual union all
select 'B' , 'D' from dual union all
select 'D' , 'E' from dual union all
select 'C' , 'F' from dual union all
select 'M' , 'B' from dual union all
select 'M' , 'L' from dual union all
select 'M' , 'N' from dual union all
select 'L' , 'Z' from dual union all
select 'Z' , 'N' from dual union all
select 'N' , 'S' from dual
)
select t.*,connect_by_isleaf leaf from t
start with t.parent1 in ('A','Z','M')
connect by prior t.child1=t.parent1
with t as (
select 'A' parent1, 'B' child1 from dual union all
select 'B' , 'C' from dual union all
select 'B' , 'D' from dual union all
select 'D' , 'E' from dual union all
select 'C' , 'F' from dual union all
select 'M' , 'B' from dual union all
select 'M' , 'L' from dual union all
select 'M' , 'N' from dual union all
select 'L' , 'Z' from dual union all
select 'Z' , 'N' from dual union all
select 'N' , 'S' from dual
)select distinct r,child1 from (
select t.*,connect_by_isleaf leaf,connect_by_root parent1 r from t
start with t.parent1 in ('A','Z','M')
connect by prior t.child1=t.parent1
) a where a.leaf=1
order by 11 A E
2 A F
3 M E
4 M F
5 M S
6 Z S
CREATE TABLE A (
PARENT VARCHAR2(10),
CHILD VARCHAR2(10)
);
统计sql:
select
parent,wm_concat(CHILD)
from A t
where t.parent in ('A','Z','M')
group by parent
order by parent
查询结果:
A B
M B,N,L
Z N
select *
from hieapp.hi_org_info
connect by prior orgcode = parentorgcode
start with orgcode = '43'
--结果
1 43 086 湖南省 1
2 4305 43 邵阳市 2
3 430528 4305 新宁县 3
4 430528100 430528 金石镇 9
5 430528100001 430528100 锦秀社区居委会 10
6 43052810000101 430528100001 金石镇锦秀社区 11
7 430528100002 430528100 刘家井社区居委会 10
8 43052810000201 430528100002 金石镇刘家井社区 11
9 430528100003 430528100 白公渡社区居委会 10
10 43052810000301 430528100003 金石镇白公渡社区 11
11 430528100004 430528100 凝秀社区居委会 10