--10g以上的话,用wm_concat()字符串连接函数,和正则表达式 with ta as( select 22 c1, 'a' c2 from dual union all select 25,'b' from dual union all select 26,'c' from dual union all select 46,'d' from dual) , tb as( select 1 c3,'"22,25,26,46"' c4 from dual) select te.a tb_c3,wm_concat(te.b) ta_c2 from ( select td.c3 a,ta.c2 b from ta,( select c3,regexp_substr(replace(c4,'"',''),'[^,]+',1,level) c5 from tb connect by level <= length(replace(c4,'"','')) - length(replace(replace(c4,'"',''),',','')) + 1) td where ta.c1 = td.c5) te group by a / TB_C3 TA_C2 ---------- -------------- 1 a,b,c,d
/* Formatted on 2011-5-19 9:09:18 (QP5 v5.114.809.3010) */ SELECT b.bid, stragg (a.aval) AS val FROM ( SELECT 22 AS aid, 'a' AS aval FROM DUAL UNION ALL SELECT 23, 'b' FROM DUAL UNION ALL SELECT 24, 'c' FROM DUAL UNION ALL SELECT 25, 'd' FROM DUAL UNION ALL SELECT 26, 'e' FROM DUAL) a, ( SELECT 1 AS bid, '"22,25,26,46"' AS bval FROM DUAL) b WHERE INSTR (b.bval, a.aid) > 0 GROUP BY b.bid 呃,10G好像不能用"STRAGG"的但是9I应该可以
SELECT B1, WM_CONCAT(A2) FROM (SELECT B1, A2 FROM (SELECT A.A1, A.A2, B.B1, B.B2 FROM A A, B B) WHERE INSTR(B2, A1) > 0) GROUP BY B1 SELECT b1,max(substr(SYS_CONNECT_BY_PATH(a2,','),2)) a2 FROM (SELECT B1, A2 ,ROWNUM rn,ROWNUM-1 rm FROM (SELECT A.A1, A.A2, B.B1, B.B2 FROM A A, B B) WHERE INSTR(B2, A1) > 0) START WITH rn = 1 CONNECT BY PRIOR rn = rm GROUP BY b1;
with ta as( select 22 c1, 'a' c2 from dual union all select 25,'b' from dual union all select 26,'c' from dual union all select 46,'d' from dual) , tb as( select 1 c3,'"22,25,26,46"' c4 from dual) select c3,a||','||b||','||c||','||d from ( select c3,max(decode(aa,'a','a',0)) a,max(decode(aa,'b','b',0)) b,max(decode(aa,'c','c',0)) c,max(decode(aa,'d','d',0)) d from ( select tb.c3,decode(sign(instr(tb.c4,ta.c1)),1,ta.c2,'') aa from ta ,tb ) group by c3 )
楼上的正解SQL> with ta as( 2 select 22 c1, 'a' c2 from dual union all 3 select 25,'b' from dual union all 4 select 26,'c' from dual union all 5 select 46,'d' from dual) 6 , tb as( 7 select 1 c3,'"22,25,26,46"' c4 from dual) 8 select c3,max(substr(sys_connect_by_path(c2,','),2)) a 9 from ( 10 select c3,c2,rownum rn,rownum - 1 rm 11 from ( 12 select a.c1,a.c2,b.c3,b.c4 13 from ta a,tb b) 14 where instr(c4,c1) > 0) 15 start with rn=1 16 connect by prior rn=rm 17 group by c3 18 /
C3 A ---------- -------------------------------------------------------------------------------- 1 a,b,c,d
我来提供一个,把代码中红色部分替换为你的B表逗号分隔字段,再去管理A表ID就可以啦。SELECT substr(a.name, b.id + 1, instr(a.name, ',', b.id + 1) - (b.id+1)) AS NAME FROM (SELECT ',' || '22,25,26,46,' || ',' AS NAME FROM dual) a, (SELECT rownum AS id FROM dual CONNECT BY rownum <= length('22,25,26,46,')) b WHERE substr(a.name, b.id, 1) = ','
--10g以上的话,用wm_concat()字符串连接函数,和正则表达式
with ta as(
select 22 c1, 'a' c2 from dual union all
select 25,'b' from dual union all
select 26,'c' from dual union all
select 46,'d' from dual)
, tb as(
select 1 c3,'"22,25,26,46"' c4 from dual)
select te.a tb_c3,wm_concat(te.b) ta_c2
from (
select td.c3 a,ta.c2 b
from ta,(
select c3,regexp_substr(replace(c4,'"',''),'[^,]+',1,level) c5
from tb
connect by
level <= length(replace(c4,'"','')) - length(replace(replace(c4,'"',''),',','')) + 1) td
where ta.c1 = td.c5) te
group by a
/
TB_C3 TA_C2
---------- --------------
1 a,b,c,d
/* Formatted on 2011-5-19 9:09:18 (QP5 v5.114.809.3010) */
SELECT b.bid, stragg (a.aval) AS val
FROM ( SELECT 22 AS aid, 'a' AS aval FROM DUAL
UNION ALL
SELECT 23, 'b' FROM DUAL
UNION ALL
SELECT 24, 'c' FROM DUAL
UNION ALL
SELECT 25, 'd' FROM DUAL
UNION ALL
SELECT 26, 'e' FROM DUAL) a,
( SELECT 1 AS bid, '"22,25,26,46"' AS bval FROM DUAL) b
WHERE INSTR (b.bval, a.aid) > 0
GROUP BY b.bid
呃,10G好像不能用"STRAGG"的但是9I应该可以
SELECT B1, WM_CONCAT(A2)
FROM (SELECT B1, A2
FROM (SELECT A.A1, A.A2, B.B1, B.B2 FROM A A, B B)
WHERE INSTR(B2, A1) > 0)
GROUP BY B1
SELECT b1,max(substr(SYS_CONNECT_BY_PATH(a2,','),2)) a2 FROM
(SELECT B1, A2 ,ROWNUM rn,ROWNUM-1 rm
FROM (SELECT A.A1, A.A2, B.B1, B.B2 FROM A A, B B)
WHERE INSTR(B2, A1) > 0)
START WITH rn = 1
CONNECT BY PRIOR rn = rm
GROUP BY b1;
select 22 c1, 'a' c2 from dual union all
select 25,'b' from dual union all
select 26,'c' from dual union all
select 46,'d' from dual)
, tb as(
select 1 c3,'"22,25,26,46"' c4 from dual)
select c3,a||','||b||','||c||','||d
from
(
select c3,max(decode(aa,'a','a',0)) a,max(decode(aa,'b','b',0)) b,max(decode(aa,'c','c',0)) c,max(decode(aa,'d','d',0)) d
from (
select tb.c3,decode(sign(instr(tb.c4,ta.c1)),1,ta.c2,'') aa from ta ,tb
)
group by c3
)
2 select 22 c1, 'a' c2 from dual union all
3 select 25,'b' from dual union all
4 select 26,'c' from dual union all
5 select 46,'d' from dual)
6 , tb as(
7 select 1 c3,'"22,25,26,46"' c4 from dual)
8 select c3,max(substr(sys_connect_by_path(c2,','),2)) a
9 from (
10 select c3,c2,rownum rn,rownum - 1 rm
11 from (
12 select a.c1,a.c2,b.c3,b.c4
13 from ta a,tb b)
14 where instr(c4,c1) > 0)
15 start with rn=1
16 connect by prior rn=rm
17 group by c3
18 /
C3 A
---------- --------------------------------------------------------------------------------
1 a,b,c,d
FROM (SELECT ',' || '22,25,26,46,' || ',' AS NAME FROM dual) a,
(SELECT rownum AS id
FROM dual
CONNECT BY rownum <= length('22,25,26,46,')) b
WHERE substr(a.name, b.id, 1) = ','