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