A表:
id name type
1 a aa,bb,cc,
2 b bb,
3 c dd,cc,B表:id name
1 aa
2 bb
3 cc
4 dd结果:
1 a aa,bb,cc, 1,2,3,
2 b bb, 2,
3 c dd,cc, 4,3,
id name type
1 a aa,bb,cc,
2 b bb,
3 c dd,cc,B表:id name
1 aa
2 bb
3 cc
4 dd结果:
1 a aa,bb,cc, 1,2,3,
2 b bb, 2,
3 c dd,cc, 4,3,
试试吧,不一定对
--建立测试数据
create table a(id number,name varchar2(10),type varchar2(1000));
insert into a values(1,'a','aa,bb,cc,');
insert into a values(2,'b','bb,');
insert into a values(3,'c','dd,cc,');
commit;
create table b(id number,name varchar2(10));
insert into b values(1,'aa');
insert into b values(2,'bb');
insert into b values(3,'cc');
insert into b values(4,'dd');
commit;
--建立function
create or replace function f_splitSTR(p_s in varchar2, p_split in varchar2)
return varchar2 is
cursor c is
select b.id
from b,
(select SUBSTR(p_s, ID, instr(p_s || p_split, p_split, ID) - ID) name
from (select level ID from dual connect by level < 8001) tb_splitSTR
WHERE ID <= LENGTH(p_s || 'a')
AND instr(p_split || p_s, p_split, ID) = ID
and SUBSTR(p_s, ID, instr(p_s || p_split, p_split, ID) - ID) is not null
order by id) t
where b.name = t.name;
Result varchar2(8000);
begin
for r in c loop
Result := Result || r.id || ',';
end loop;
return(Result);
end;--查询
select a.id, a.name, f_splitSTR(a.type, ',') ids from a; ID NAME IDS
1 1 a 1,2,3,
2 2 b 2,
3 3 c 4,3,
2 (
3 SELECT 1 AS ID, 'a' AS NAME, 'aa,bb,cc,' AS TYPE FROM DUAL UNION
4 SELECT 2 AS ID, 'b' AS NAME, 'bb,' AS TYPE FROM DUAL UNION
5 SELECT 3 AS ID, 'c' AS NAME, 'dd,cc,' AS TYPE FROM DUAL
6 ),
7 B AS
8 (
9 SELECT 1 AS ID, 'aa' AS NAME FROM DUAL UNION
10 SELECT 2 AS ID, 'bb' AS NAME FROM DUAL UNION
11 SELECT 3 AS ID, 'cc' AS NAME FROM DUAL UNION
12 SELECT 4 AS ID, 'dd' AS NAME FROM DUAL
13 )
14 SELECT DISTINCT RR.ID,
15 RR.NAME,
16 RR.TYPE,
17 (WMSYS.WM_CONCAT(B_ID) OVER(PARTITION BY RR.ID ))||',' AS IDS
18 FROM (SELECT R.ID,
19 R.NAME,
20 R.TYPE,
21 R.B_ID
22 FROM (SELECT A.ID,
23 A.NAME,
24 A.TYPE,
25 INSTR(','||A.TYPE , ---------------- 前后并上',',来解决问题
26 ','||B.NAME ) AS IN_FLAG,
27 B.ID AS B_ID
28 FROM A,
29 B ---
30 ) R
31 WHERE R.IN_FLAG > 0
32 ORDER BY R.ID,R.IN_FLAG ---
33 ) RR
34 ORDER BY RR.ID
35 ;
ID NAME TYPE IDS
---------- ---- --------- --------------------------------------------------------------------------------
1 a aa,bb,cc, 1,2,3,
2 b bb, 2,
3 c dd,cc, 4,3,