有表a:
id fid
----------------
1 f1,f2
2 f4,f5,f6表b:
fid fname
-------------
f1 name1
f2 name2
f3 name3
f4 name4
.. .....查询结果为:
id fid fname
------------------------------------
1 f1,f2 name1,name2
2 f4,f5,f6 name4,name5,name6
id fid
----------------
1 f1,f2
2 f4,f5,f6表b:
fid fname
-------------
f1 name1
f2 name2
f3 name3
f4 name4
.. .....查询结果为:
id fid fname
------------------------------------
1 f1,f2 name1,name2
2 f4,f5,f6 name4,name5,name6
--呵呵 满足题目要求 但是不切实际
select id,fid,replace(FID,'f','name') fname from tb1
--修改了一下,这个应该可以
declare
sql_str varchar2(32767);
begin
sql_str:='create table tab_long (';
for i in 0..23 loop
for j in 0..11 loop
sql_str:=sql_str||'v'||lpad(i,2,0)||lpad(j,2,0)||' varchar2(10)'||',' ;
end loop;
end loop;
sql_str:=rtrim(sql_str,',');
sql_str:=sql_str||')';
execute immediate sql_str;
end;
--刚忘了乘5了
declare
sql_str varchar2(32767);
begin
sql_str:='create table tab_long (';
for i in 0..23 loop
for j in 0..11 loop
sql_str:=sql_str||'v'||lpad(i,2,0)||lpad(5*j,2,0)||' varchar2(10)'||',' ;
end loop;
end loop;
sql_str:=rtrim(sql_str,',');
sql_str:=sql_str||')';
execute immediate sql_str;
end;
select a.id,a.fid,wm_concat(b.fname)
from a ,b
where a.fid like '%'||b.fid||'%'
group by a.id,a.fid
insert into a (id,fid)
select 1,'fi,f2' from dual
union
select 2,'f4,f5,f6' from dual
commit;
create table b (fid varchar2(50),fname varchar2(50));
insert into b(fid,fname)
select 'f1','name1' from dual
union
select 'f2','name2' from dual
union
select 'f3','name3' from dual
union
select 'f4','name4' from dual
union
select 'f5','name5' from dual
union
select 'f6','name6' from dual
commit;select a.id,a.fid,wm_concat(b.fname)
from a ,b
where a.fid like '%'||b.fid||'%'
group by a.id,a.fid
with ta as(
select 1 id,'f1,f2' fid from dual union all
select 2,'f4,f5,f6' from dual)
,tb as(
select 'f1' fid,'name1' fname from dual union all
select 'f2','name2' from dual union all
select 'f3','name3' from dual union all
select 'f4','name4' from dual union all
select 'f5','name5' from dual union all
select 'f6','name6' from dual)
select ta.id id,
ta.fid fid,
wm_concat(tb.fname) fname
from ta,tb
where instr(ta.fid,tb.fid)>0
group by ta.id,ta.fid;
ID FID FNAME
---------- -------- --------------------------------------------
1 f1,f2 name1,name2
2 f4,f5,f6 name4,name6,name5
oracle 解决匹配的几种方法