或子句返回不正确的结果

Question

我正在一个包含用户名和用户ids的表上写一个查询。我想忽略包含以下任何字符串的用户名：

锁定，停用，关闭

以用户id为1234且用户名"myusername锁定“的记录为例。

下面的查询仍然返回此记录

select username 
from table where (
instr(username),'LOCKED') = 0
or instr(username),'CLOSED') = 0
or instr(username),'DEACTIVATED') = 0
) and userid = '1234'

我预计不会返回任何结果，因为虽然用户id 1234确实存在，但如果没有用户名中的字符串“锁定”，它就不存在。

为什么会有记录回来？

是因为其他条件都是真的吗？即。有一个id 1234的记录并且用户名不包含“关闭”？(因为它包含“锁定”

Answer 1

如果任何条件都是or，则true运算符返回true。也就是说，即使其中一个是false，但另外两个是true，结果将是true

或：

false true true -> true
false false true -> true

另一方面，如果任何条件为and，则false运算符返回false。

和：

false true true -> false
false false true -> false

我想你想要的是AND操作符。在你的脑子里想“如果Y是真的，如果X是true___”。您之前得到的是“如果Y为真或X为true___"，这当然是返回true。

因此，解决办法是：

select username 
from table
where (instr(username), 'LOCKED') = 0 and
      instr(username), 'CLOSED') = 0 and
      instr(username), 'DEACTIVATED') = 0
     ) and
    userid = '1234'

Answer 2

Gordon_Linoff有正确的答案，但我喜欢使用公共表表达式(CTE)，因此我想添加这个额外的答案，以防您希望将排除名称作为CSV提供，而不是将其硬编码到SQL中。

   WITH
    user_table
    AS
        -- set up some usernames for testing
        (SELECT 'ARBY' AS username
           FROM DUAL
         UNION ALL
         SELECT 'CLOSED'
           FROM DUAL
         UNION ALL
         SELECT 'GOOFY'
           FROM DUAL),
    csv_value
    AS
        (SELECT 'LOCKED, CLOSED, DEACTIVATED' csv
           FROM DUAL),
    exclude_uservalues
    AS
        --  1) Bracket CSV value with commas
        --  2) Remove any spaces (presumes no embedded spaces within CSV values)
        (SELECT ',' || REPLACE (csv, ' ', NULL) || ',' AS csv_values
           FROM csv_value),
    exclude_userset (
                        exclude_user, csv_values
                    )
    AS
        -- This common table expression will split the CSV values into separate records
        -- It works by
        --   a) extracting the value between the first two commas
        --   b) dropping everything before the second comma
        --   c) terminating when there is no second comma
        (SELECT SUBSTR (csv_values, 2, INSTR (csv_values, ',', 2) - 2) AS exclude_user
              , SUBSTR (csv_values, INSTR (csv_values, ',', 2)) AS csv_values
           FROM exclude_uservalues
         UNION ALL
         SELECT SUBSTR (csv_values, 2, INSTR (csv_values, ',', 2) - 2) AS exclude_user
              , SUBSTR (csv_values, INSTR (csv_values, ',', 2)) AS csv_values
           FROM exclude_userset
          WHERE INSTR (csv_values, ',', 2) > 0)
SELECT username
  FROM user_table LEFT OUTER JOIN exclude_userset ON username = exclude_user
 WHERE exclude_user IS NULL;

这种胡说八道的结果是：

USERNAME
--------
GOOFY
ARBY