在 NOT IN 子句中传递分隔字符串

Question

下面的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');

Answer 1

您可以使用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)

Answer 2

'BOB,JOHN'不是两个字符串值的列表，它是一个字符串值，恰好在字符串中包含逗号，并且：

'JACK' = 'BOB,JOHN'
'JOHN' = 'BOB,JOHN'
'BOB'  = 'BOB,JOHN'

全部为 false，因此查询将返回与NOT IN筛选器匹配的所有行。

您可以使用分隔符将值和列表括起来，并测试该值是否不是列表的子字符串，如下所示：

SELECT *
FROM (
SELECT 'JACK' AS VALUE FROM DUAL UNION ALL
SELECT 'JOHN' AS VALUE FROM DUAL UNION ALL
SELECT 'BOB'  AS VALUE FROM DUAL
)
WHERE INSTR( ',' || 'BOB,JOHN' || ',', ',' || value || ',' ) = 0

或者，可以使用用户定义的集合：

CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(20);

然后使用MEMBER OF运算符测试值是否为集合的成员：

SELECT *
FROM (
SELECT 'JACK' AS VALUE FROM DUAL UNION ALL
SELECT 'JOHN' AS VALUE FROM DUAL UNION ALL
SELECT 'BOB'  AS VALUE FROM DUAL
)
WHERE  VALUE NOT MEMBER OF StringList( 'BOB', 'JOHN' );