我有一个表:其有中两个字段
child parent
1 0
2 1
3 1
4 0
5 4
6 4
7 2
....也就是说,这两个字段就是父子关系字段,
我现在达到的目的是:按child进行聚类,有关系的都放到一个类里,0为最上层。
如上面几个字段:
就生成:
1,2,3,7
4,5,6
两行
也就是说,同一行内的child都找到关系关联起来。
child parent
1 0
2 1
3 1
4 0
5 4
6 4
7 2
....也就是说,这两个字段就是父子关系字段,
我现在达到的目的是:按child进行聚类,有关系的都放到一个类里,0为最上层。
如上面几个字段:
就生成:
1,2,3,7
4,5,6
两行
也就是说,同一行内的child都找到关系关联起来。
with tb as(
select 1 child,0 parent from dual union all
select 2, 1 from dual union all
select 3, 1 from dual union all
select 4, 0 from dual union all
select 5, 4 from dual union all
select 6, 4 from dual union all
select 7, 2 from dual)
select newparent,wm_concat(child) newchild
from (select connect_by_root(child) newparent,child
from tb
connect by prior child=parent
start with parent=0)
group by newparent
NEWPARENT NEWCHILD
---------- --------------------
1 1,2,7,3
4 4,5,6
SELECT wm_concat(child)
FROM (SELECT LEVEL lv,
child,
PARENT,
substr(sys_connect_by_path(t.child, ','),
2,
instr(sys_connect_by_path(t.child, ',') || ',', ',', 2) - 2) rootid
FROM t
START WITH PARENT = 0
CONNECT BY PRIOR t.child = t.parent)
GROUP BY rootid;
SQL> with tb as(
2 select 1 child,0 parent from dual union all
3 select 2, 1 from dual union all
4 select 3, 1 from dual union all
5 select 4, 0 from dual union all
6 select 5, 4 from dual union all
7 select 6, 4 from dual union all
8 select 7, 2 from dual)
9 select wm_concat(child),substr(str,2,instr(str||',',',',1,2)-instr(str||',',',',1)-1)
10 from (select child,parent,sys_connect_by_path(child,',') str
11 from tb
12 start with parent=0
13 connect by prior child= parent )
14 group by substr(str,2,instr(str||',',',',1,2)-instr(str||',',',',1)-1)
15 /
WM_CONCAT(CHILD) SUBSTR(STR,2,INSTR(STR||',',',
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1,2,7,3 1
4,5,6 4
SELECT ltrim(MAX(sys_connect_by_path(child, ',')), ',')
FROM (SELECT row_number() over(PARTITION BY rootid ORDER BY rootid, lv) rn,
child,rootid
FROM (SELECT child,LEVEL lv,
substr(sys_connect_by_path(t.child, ','),2,
instr(sys_connect_by_path(t.child, ',') || ',',
',',2) - 2) rootid
FROM t
START WITH PARENT = 0
CONNECT BY PRIOR t.child = t.parent))
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1
AND rootid = PRIOR rootid
GROUP BY rootid;
SQL> with tb as(
2 select 1 child,0 parent from dual union all
3 select 2, 1 from dual union all
4 select 4, 0 from dual union all
5 select 3, 1 from dual union all
6 select 5, 4 from dual union all
7 select 6, 4 from dual union all
8 select 7, 2 from dual)
9 select newparent,wm_concat(child) newchild
10 from (select connect_by_root(child) newparent,child
11 from tb
12 connect by prior child=parent
13 start with parent=0
14 order by level)
15 group by newparent;
NEWPARENT NEWCHILD
---------- --------------------------------------------------------------------------------
1 1,2,3,7
4 4,6,5
SQL>
如果数据是这样的:child parent
1 0
2 1
3 1
4 0
5 4
6 4
7 2
5 1那正确的结果便要是:
1,2,3,4,5,6,7 因为通过5,把1,4又连起来了。但现在结果却是:
1,2,3,7,5
4,5,6
你到底是什么规律你这样还不如直接distinct 在wm_concat
with tb as(
select 1 child,0 parent from dual union all
select 2, 1 from dual union all
select 3, 1 from dual union all
select 4, 0 from dual union all
select 5, 4 from dual union all
select 6, 4 from dual union all
select 7, 2 from dual)
select newparent,wm_concat(child) newchild
from (select connect_by_root(child) newparent,child
from tb
connect by nocycle prior child=parent --加个nocycle 取消循环
start with parent=0)
group by newparent