下面的 SQL 从概念上复制了我试图解决的问题。尽管传递了 NOT IN 子句，但仍返回所有三个记录。
SELECT * FROM (
SELECT 'JACK' AS VALUE FROM DUAL
UNION
SELECT 'JOHN' AS VALUE FROM DUAL
UNION
SELECT 'BOB' AS VALUE FROM DUAL
) WHERE VALUE NOT IN (SELECT 'BOB,JOHN' FROM DUAL);
我有一个表，其中包含一个分隔字符串，我想将其用作从数据集中排除记录的条件。但是，我遇到的问题是返回的字符串没有分解为其分隔项。我想把上面的内容翻译成：
SELECT * FROM (
SELECT 'JACK' AS VALUE FROM DUAL
UNION
SELECT 'JOHN' AS VALUE FROM DUAL
UNION
SELECT 'BOB' AS VALUE FROM DUAL
) WHERE VALUE NOT IN ('BOB','JOHN');
您可以使用 regexp_substr 来解决该问题：
SELECT * FROM (
SELECT 'JACK' AS VALUE FROM DUAL
UNION
SELECT 'JOHN' AS VALUE FROM DUAL
UNION
SELECT 'BOB' AS VALUE FROM DUAL
)
WHERE VALUE NOT IN (SELECT regexp_substr('BOB,JOHN','[^,]+', 1, LEVEL) FROM dual CONNECT BY regexp_substr('BOB,JOHN', '[^,]+', 1, LEVEL) IS NOT NULL)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
