假如现在有两张表: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语句,谢谢了!
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语句,谢谢了!
select a.sid,a.sname,wm_concat(b.subname) subname
from stuinfo a,subinfo b
where instr(a.subs,'|'||b.subid(+)||'|')>0
group by a.sid,a.sname
10g以上的可以这么写
select sid,sname,substr(max(sys_connect_by_path(subname,',')),2) subname
from (
select a.sid,a.sname,b.subname,
row_number()over(partition by a.sid order by rownum)rn
from stuinfo a,subinfo b
where instr(a.subs,'|'||b.subid(+)||'|')>0)
connect by prior rn=rn-1 and prior sid=sid
start with rn=1
group by sid,sname
from stuinfo a,subinfo b
where instr(a.subs,'|'||b.subid(+)||'|')>0
group by a.sid,a.sname
order by sid
大概就是这样