设计有问题,简单的问题复杂化。不过当作业做还是可以的:
select SUBSTR(XUHS,
case when POS=1 then 1
else INSTR(XUHS || ',',',',1,POS-1)+1
end,
case when POS=1 then INSTR(XUHS || ',',',',1,POS)-1
else INSTR(XUHS || ',',',',1,POS)-INSTR(XUHS || ',',',',1,POS-1)-1
end
) XUH,SUBJECT
from (select XUH xuhs,length(XUH)-length(replace(XUH,',',''))+1 NUMS,SUBJECT from T2) a,
(select rownum POS from T1) B
where B.POS<=a.NUMS
order by XUH,SUBJECT;
如果是9i以下,建议写个函数处理好一点。
如果10G以上,用这个不会有问题了:
select xuh,
(select wm_concat(name) from t1
where id in
(select case when instr(xuh,',')=0 then xuh else regexp_substr(xuh,'[^,]',1,level) end
from dual connect by level<=length(xuh)-length(replace(xuh,',',''))+1))
as name,subject
from t2
select xuh,wm_concat(name) name, subject
from (
select t2.xuh,t1.id,t1.name,t2.subject
from t2
join t1
where instr(t2.xuh,t1.id) > 0
order by t1.id) tt
group by xuh,subject;