本帖最后由 forgetbefore 于 2012-11-01 10:31:44 编辑
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 * FROM t;
现在有2列数据，都是从0～4里面随机抽取，个数未知，不重复出现，现在要找出a列里面是否包含b列的数字。
假设有如上数据，则结果应该是
1 '0,' '0,4,' 'Y'
2 '1,4,' '1,2,3,' 'Y'
3 '2,3,' '0,' 'N'
4 '1,2,4,' '0,3,' 'N'
5 '1,2,' '0,2,4,' 'Y'
求sql如何写。
------最佳解决方案--------------------
下午有空，帮你想了个简单的。
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
------其他解决方案--------------------
没有什么好办法么
------其他解决方案--------------------
SQL有点坑爹 XD
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,
DECODE(decode(decode(sign(INSTR(a, '0,')),1,1,0) + decode(sign(INSTR(b, '0,')),1,1,0),2,1,0) +