3,490
社区成员
发帖
与我相关
我的任务
分享
WITH t AS(
SELECT '2,1' a,'1414,1345,4442' b FROM dual
UNION ALL SELECT '1,2,10','11,22' FROM dual)
,tmp1 AS(
SELECT DISTINCT SUBSTR(t.a,INSTR(','||t.a,',',1,b.rn),
instr(t.a||',',',',1,b.rn)-INSTR(','||t.a,',',1,b.rn)) a1
FROM t,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM<=5) b
WHERE LENGTH(t.a)-LENGTH(REPLACE(t.a,','))>=b.rn-1)
,tmp2 AS(
SELECT DISTINCT SUBSTR(t.b,INSTR(','||t.b,',',1,c.rn),
instr(t.b||',',',',1,c.rn)-INSTR(','||t.b,',',1,c.rn)) b1
FROM t,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM<=10) c
WHERE LENGTH(t.b)-LENGTH(REPLACE(t.b,','))>=c.rn-1)
SELECT tmp1.a1,tmp2.b1 FROM tmp1,tmp2,t
WHERE ','||t.a||',' LIKE '%,'||tmp1.a1||',%'
AND ','||t.b||',' LIKE '%,'||tmp2.b1||',%'
WITH t AS(
SELECT '2,1' a,'1414,1345,4442' b FROM dual
UNION ALL SELECT '1,2,10','11,22' FROM dual)
,t1 as (select distinct
substr(regexp_substr(',' || a, ',([^,]+)', 1, level), 2) as "a"
from t
connect by level <= length(regexp_replace(a, '[^,]', '')) + 1)
,t2 as (select distinct
substr(regexp_substr(',' || b, ',([^,]+)', 1, level), 2) as "b"
from t
connect by level <= length(regexp_replace(b, '[^,]', '')) + 1)
select * from t1,t2