一条SQL语句，关于字符分割关联多条记录的问题

原文传送门：http://topic.csdn.net/u/20091010/14/FC7737C1-D60B-43F1-A8B5-A9EEF2DE4426.html

假如现在有两张表：

1.表stuinfo
sid sname subs
1  jack  |1|2|
2  marry |1|4|
3  tom  |3|

2.表subinfo
subid  subname
1      physics
2      maths
3      biology
4      geography

我想把stuinfo的subs字段按｜字符分开，然后匹配subinfo的subid，取subname的值，匹配到多个的话就用逗号隔开。
简而言之我想得到的结果如下：
sid  sname subname
1    jack  physics,maths
2    marry physics,geography
3    tom  biology

希望各位大侠能帮忙设计一下SQL语句，谢谢了！

 

Oracle 10g以上版本

[xhtml] view plaincopyprint?

1. select a.sid,a.sname,wm_concat(b.subname) subname  
2.   from stuinfo a,subinfo b  
3. where instr(a.subs,'|'||b.subid(+)||'|')>0  
4. group by a.sid,a.sname  

select a.sid,a.sname,wm_concat(b.subname) subname from stuinfo a,subinfo bwhere instr(a.subs,'|'||b.subid(+)||'|')>0group by a.sid,a.sname

9i的

 

[java] view plaincopyprint?

1. select sid,sname,substr(max(sys_connect_by_path(subname,',')),2) subname  
2.   from (  
3.   select a.sid,a.sname,b.subname,  
4.     row_number()over(partition by a.sid order by rownum)rn  
5.   from stuinfo a,subinfo b  
6. where instr(a.subs,'|'||b.subid(+)||'|')>0)  
7. connect by prior rn=rn-1 and prior sid=sid  
8. start with rn=1  
9. group by sid,sname