先看单表,再完成多表链接的情况 看看这个是不是你想要的。SQL> select * from a;I NAM - --- 1 aa 1 bb 1 cc 2 xx 2 yy 3 zz 3 mm select max(substr((sys_connect_by_path(name,',')),2)) cola from ( select id,name, rownum rnum, row_number() over(partition by id order by id) rn1 from a ) start with rn1=1 connect by rnum-1=prior rnum group by id ;--------------------------------------------------------------------------------aa,bb,cc xx,yy zz,mm
多谢各位大人的回答 To:lexchi() ( ) 我是希望不用存储过程可以达到这种效果. To:kinglht(爱新觉罗至尊宝) 我看你的blog,写得挺好,但也是通过存储过程来实现. To:jdsnhan(柳荫凉) 多谢你的回答,我建表测试后发现,结果是不正确的,请你看一下. 表内数据: ID NAME 1 a1 1 b1 2 a2 3 a3 4 a4 1 c1 3 b3 sql语句执行结果: ID COLA 1 c1 2 a2 3 c1,b3 4 a4 ID = 1 和ID = 3 的结果明显不正确.
呵呵,其中一个问题需要注意一下,按路径搜索的内容必须是有序的,否则,结果就会乱 改装一下: 1 select max(substr((sys_connect_by_path(name,',')),2)) cola,id 2 from ( 3 select id,name,rownum rnum,rn1 from ( 4 select id,name, 5 row_number() over(partition by id order by id) rn1 6 from a order by 1) 7 ) 8 start with rn1=1 9 connect by rnum-1=prior rnum 10* group by id 11 ;COLA ID -------------------- -- a1,b1,c1 1 a2 2 a3,b3 3 a4 4
COLA ID c1,b1,a1 1 c1,b1,a1,a2 2 c1,b1,a1,a2,b3,a3,a4 4 c1,b1,a1,a2,b3,a3 3
:( 结果还是不正常. 按路径搜索的内容必须是有序的 表中的ID确实是有序的. COLA ID c1,b1,a1 1 c1,b1,a1,a2 2 c1,b1,a1,a2,b3,a3,a4 4 c1,b1,a1,a2,b3,a3 3
http://blog.csdn.net/kinglht
看看这个是不是你想要的。SQL> select * from a;I NAM
- ---
1 aa
1 bb
1 cc
2 xx
2 yy
3 zz
3 mm select max(substr((sys_connect_by_path(name,',')),2)) cola
from (
select id,name,
rownum rnum,
row_number() over(partition by id order by id) rn1
from a
)
start with rn1=1
connect by rnum-1=prior rnum
group by id
;--------------------------------------------------------------------------------aa,bb,cc
xx,yy
zz,mm
To:lexchi() ( )
我是希望不用存储过程可以达到这种效果.
To:kinglht(爱新觉罗至尊宝)
我看你的blog,写得挺好,但也是通过存储过程来实现.
To:jdsnhan(柳荫凉)
多谢你的回答,我建表测试后发现,结果是不正确的,请你看一下.
表内数据: ID NAME
1 a1
1 b1
2 a2
3 a3
4 a4
1 c1
3 b3 sql语句执行结果:
ID COLA
1 c1
2 a2
3 c1,b3
4 a4 ID = 1 和ID = 3 的结果明显不正确.
改装一下: 1 select max(substr((sys_connect_by_path(name,',')),2)) cola,id
2 from (
3 select id,name,rownum rnum,rn1 from (
4 select id,name,
5 row_number() over(partition by id order by id) rn1
6 from a order by 1)
7 )
8 start with rn1=1
9 connect by rnum-1=prior rnum
10* group by id
11 ;COLA ID
-------------------- --
a1,b1,c1 1
a2 2
a3,b3 3
a4 4
c1,b1,a1 1
c1,b1,a1,a2 2
c1,b1,a1,a2,b3,a3,a4 4
c1,b1,a1,a2,b3,a3 3
按路径搜索的内容必须是有序的 表中的ID确实是有序的.
COLA ID
c1,b1,a1 1
c1,b1,a1,a2 2
c1,b1,a1,a2,b3,a3,a4 4
c1,b1,a1,a2,b3,a3 3
多谢jdsnhan(柳荫凉) 的耐心回答.