SQL有点坑爹 XDWITH t AS ( SELECT '0,' AS a, '0,4,' AS b FROM dual union all SELECT '1,4,' AS a, '1,2,3,' AS b FROM dual union all SELECT '2,3,' AS a, '0,' AS b FROM dual union all SELECT '1,2,4,' AS a, '0,3,' AS b FROM dual union all SELECT '1,2,' AS a, '0,2,4,' AS b FROM dual ) select a, b, DECODE(decode(decode(sign(INSTR(a, '0,')),1,1,0) + decode(sign(INSTR(b, '0,')),1,1,0),2,1,0) + decode(decode(sign(INSTR(a, '1,')),1,1,0) + decode(sign(INSTR(b, '1,')),1,1,0),2,1,0) + decode(decode(sign(INSTR(a, '2,')),1,1,0) + decode(sign(INSTR(b, '2,')),1,1,0),2,1,0) + decode(decode(sign(INSTR(a, '3,')),1,1,0) + decode(sign(INSTR(b, '3,')),1,1,0),2,1,0) + decode(decode(sign(INSTR(a, '4,')),1,1,0) + decode(sign(INSTR(b, '4,')),1,1,0),2,1,0) + decode(decode(sign(INSTR(a, '5,')),1,1,0) + decode(sign(INSTR(b, '5,')),1,1,0),2,1,0) + decode(decode(sign(INSTR(a, '6,')),1,1,0) + decode(sign(INSTR(b, '6,')),1,1,0),2,1,0) + decode(decode(sign(INSTR(a, '7,')),1,1,0) + decode(sign(INSTR(b, '7,')),1,1,0),2,1,0) + decode(decode(sign(INSTR(a, '8,')),1,1,0) + decode(sign(INSTR(b, '8,')),1,1,0),2,1,0) + decode(decode(sign(INSTR(a, '9,')),1,1,0) + decode(sign(INSTR(b, '9,')),1,1,0),2,1,0),1,'Y','N') as flg from t
WITH t AS ( SELECT '0,' AS a, '0,4,' AS b FROM dual union all SELECT '1,4,' AS a, '1,2,3,' AS b FROM dual union all SELECT '2,3,' AS a, '0,' AS b FROM dual union all SELECT '1,2,4,' AS a, '0,3,' AS b FROM dual union all SELECT '1,2,' AS a, '0,2,4,' AS b FROM dual ) SELECT a,b, ( CASE WHEN INSTR(a,'0')>0 AND INSTR(b,'0')>0 THEN 'Y' WHEN INSTR(a,'1')>0 AND INSTR(b,'1')>0 THEN 'Y' WHEN INSTR(a,'2')>0 AND INSTR(b,'2')>0 THEN 'Y' WHEN INSTR(a,'3')>0 AND INSTR(b,'3')>0 THEN 'Y' WHEN INSTR(a,'4')>0 AND INSTR(b,'4')>0 THEN 'Y' ELSE 'N' END ) AS flag FROM t; 这个和2楼的比,哪个效率高?
下午有空,帮你想了个简单的。WITH t AS ( SELECT '0,' AS str1, '0,4,' AS str2 FROM dual union all SELECT '1,4,' AS str1, '1,2,3,' AS str2 FROM dual union all SELECT '2,3,' AS str1, '0,' AS str2 FROM dual union all SELECT '1,2,4,' AS str1, '0,3,' AS str2 FROM dual union all SELECT '1,2,' AS str1, '0,2,4,' AS str2 FROM dual )select str1,str2,DECODE(SIGN(INSTR(translate(replace(str1,',',''),replace(str2,',',''),'XXX'),'X')),1,'Y','N') AS STR3 FROM T
SELECT n.a, n.b, DECODE(n.flag,0,'Y','N') flag FROM (SELECT m.a, m.b, m.flag, ROW_NUMBER() OVER(PARTITION BY m.a ORDER BY m.flag) rn FROM (SELECT a, b, LEVEL AS lev, CASE WHEN INSTR(',' || a, ',' || RTRIM(REGEXP_SUBSTR(b || ',', '.*?' || ',', 1, LEVEL), ',') || ',') > 0 THEN 0 ELSE 1 END flag FROM t CONNECT BY a = CONNECT_BY_ROOT a AND LEVEL <= LENGTH(REGEXP_REPLACE(b || ',', '[^' || ',' || ']', NULL)) ) m ) n WHERE n.rn = 1
(
SELECT '0,' AS a, '0,4,' AS b FROM dual
union all
SELECT '1,4,' AS a, '1,2,3,' AS b FROM dual
union all
SELECT '2,3,' AS a, '0,' AS b FROM dual
union all
SELECT '1,2,4,' AS a, '0,3,' AS b FROM dual
union all
SELECT '1,2,' AS a, '0,2,4,' AS b FROM dual
) select a,
b,
DECODE(decode(decode(sign(INSTR(a, '0,')),1,1,0) + decode(sign(INSTR(b, '0,')),1,1,0),2,1,0) +
decode(decode(sign(INSTR(a, '1,')),1,1,0) + decode(sign(INSTR(b, '1,')),1,1,0),2,1,0) +
decode(decode(sign(INSTR(a, '2,')),1,1,0) + decode(sign(INSTR(b, '2,')),1,1,0),2,1,0) +
decode(decode(sign(INSTR(a, '3,')),1,1,0) + decode(sign(INSTR(b, '3,')),1,1,0),2,1,0) +
decode(decode(sign(INSTR(a, '4,')),1,1,0) + decode(sign(INSTR(b, '4,')),1,1,0),2,1,0) +
decode(decode(sign(INSTR(a, '5,')),1,1,0) + decode(sign(INSTR(b, '5,')),1,1,0),2,1,0) +
decode(decode(sign(INSTR(a, '6,')),1,1,0) + decode(sign(INSTR(b, '6,')),1,1,0),2,1,0) +
decode(decode(sign(INSTR(a, '7,')),1,1,0) + decode(sign(INSTR(b, '7,')),1,1,0),2,1,0) +
decode(decode(sign(INSTR(a, '8,')),1,1,0) + decode(sign(INSTR(b, '8,')),1,1,0),2,1,0) +
decode(decode(sign(INSTR(a, '9,')),1,1,0) + decode(sign(INSTR(b, '9,')),1,1,0),2,1,0),1,'Y','N') as flg
from t
WITH t AS
(
SELECT '0,' AS a, '0,4,' AS b FROM dual
union all
SELECT '1,4,' AS a, '1,2,3,' AS b FROM dual
union all
SELECT '2,3,' AS a, '0,' AS b FROM dual
union all
SELECT '1,2,4,' AS a, '0,3,' AS b FROM dual
union all
SELECT '1,2,' AS a, '0,2,4,' AS b FROM dual
)
SELECT a,b,
(
CASE WHEN INSTR(a,'0')>0 AND INSTR(b,'0')>0 THEN 'Y'
WHEN INSTR(a,'1')>0 AND INSTR(b,'1')>0 THEN 'Y'
WHEN INSTR(a,'2')>0 AND INSTR(b,'2')>0 THEN 'Y'
WHEN INSTR(a,'3')>0 AND INSTR(b,'3')>0 THEN 'Y'
WHEN INSTR(a,'4')>0 AND INSTR(b,'4')>0 THEN 'Y'
ELSE 'N' END
) AS flag
FROM t;
这个和2楼的比,哪个效率高?
(
SELECT '0,' AS str1, '0,4,' AS str2 FROM dual
union all
SELECT '1,4,' AS str1, '1,2,3,' AS str2 FROM dual
union all
SELECT '2,3,' AS str1, '0,' AS str2 FROM dual
union all
SELECT '1,2,4,' AS str1, '0,3,' AS str2 FROM dual
union all
SELECT '1,2,' AS str1, '0,2,4,' AS str2 FROM dual
)select str1,str2,DECODE(SIGN(INSTR(translate(replace(str1,',',''),replace(str2,',',''),'XXX'),'X')),1,'Y','N') AS STR3 FROM T
SELECT n.a,
n.b,
DECODE(n.flag,0,'Y','N') flag
FROM (SELECT m.a,
m.b,
m.flag,
ROW_NUMBER() OVER(PARTITION BY m.a ORDER BY m.flag) rn
FROM (SELECT a,
b,
LEVEL AS lev,
CASE WHEN INSTR(',' || a, ',' || RTRIM(REGEXP_SUBSTR(b || ',', '.*?' || ',', 1, LEVEL), ',') || ',') > 0 THEN 0 ELSE 1 END flag
FROM t
CONNECT BY a = CONNECT_BY_ROOT a
AND LEVEL <= LENGTH(REGEXP_REPLACE(b || ',', '[^' || ',' || ']', NULL))
) m
) n
WHERE n.rn = 1