又是这个问题,下面方法在9i下可行
SQL> select * from a; A_ID NAME_A
----------- --------------------
1 a
2 b
3 cSQL> select * from b; B_ID A_ID NAME_B
----------- ----------- --------------------
101 1 dfs
102 1 hte
103 1 og3
104 2 fe32
105 3 d2
106 3 r35
107 3 1fg7 rows selectedselect a_id, substr(max(sys_connect_by_path(name_b, ',')),2) result
from (select a.a_id,
b.name_b,
b.b_id,
(row_number() over(order by a.a_id, b.b_id) + dense_rank()
over(order by a.a_id)) rn,
min(b.b_id) over(partition by a.a_id) qs
from a,b where a.a_id = b.a_id)
start with b_id = qs
connect by rn - 1 = prior rn
group by a_idSQL>
12 / A_ID RESULT
----------- --------------------------------------------------------------------------------
1 dfs,hte,og3
2 fe32
3 d2,r35,1fg
SQL> select * from a; A_ID NAME_A
----------- --------------------
1 a
2 b
3 cSQL> select * from b; B_ID A_ID NAME_B
----------- ----------- --------------------
101 1 dfs
102 1 hte
103 1 og3
104 2 fe32
105 3 d2
106 3 r35
107 3 1fg7 rows selectedselect a_id, substr(max(sys_connect_by_path(name_b, ',')),2) result
from (select a.a_id,
b.name_b,
b.b_id,
(row_number() over(order by a.a_id, b.b_id) + dense_rank()
over(order by a.a_id)) rn,
min(b.b_id) over(partition by a.a_id) qs
from a,b where a.a_id = b.a_id)
start with b_id = qs
connect by rn - 1 = prior rn
group by a_idSQL>
12 / A_ID RESULT
----------- --------------------------------------------------------------------------------
1 dfs,hte,og3
2 fe32
3 d2,r35,1fg
http://community.csdn.net/Expert/topic/4161/4161213.xml?temp=.3643457