vba SQL：喜欢和不喜欢的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3198736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: problems with like and NOT LIKE
提问by l--''''''---------''''''''''''
i have this statement in access:
我在访问中有此声明：
SELECT *
FROM accountsnew
WHERE [Panels] not like '*IT_OXYC,*' and [Panels] not like '*OXY_SN,*' and [Panels] not like '*OXY_S,*' and [Panels] not like '*OXY_N,*' and [Panels] like '*OXYC_SNEG,*' or [Panels] like '*OXYC_PNEG,*';
for some reason it is not returning the proper dataset. when i look more deeply at the data returned, using excel, i see that what it returned in fact DID contain IT_OXYC,and some other parameters.
由于某种原因，它没有返回正确的数据集。当我使用 excel 更深入地查看返回的数据时，我发现它返回的数据实际上包含 DIDIT_OXYC,和其他一些参数。
is there something wrong with my statement?
我的说法有问题吗？
i am trying to get all records which do not have IT_OXYC,OXY_SN, OXY_S,etc... and DO have either OXYC_SNEG,or OXYC_PNEG,
我正在尝试获取所有没有的记录IT_OXYC,OXY_SN，OXY_S,etc...并且确实有OXYC_SNEG,或OXYC_PNEG,
回答by Zaahid
It's probably just precedence... define it with braces to set the precedence: WHERE (panels NOT LIKE '1' AND panels NOT LIKE '2') AND (panels LIKE '3' OR panels LIKE '4')
它可能只是优先级......用大括号定义它以设置优先级：WHERE（面板不喜欢'1'和面板不喜欢'2'）和（面板喜欢'3'或面板喜欢'4'）
回答by Mark Byers
Your logic is wrong. Translating your request bit-by-bit:
你的逻辑是错误的。一点一点地翻译您的请求：
I am trying to get all records
我正在尝试获取所有记录
SELECT * FROM accountsnew
SELECT * FROM 帐户新
which do not
哪些不
WHERE NOT (...)
如果不是（...）
have IT_OXYC or OXY_SN or OXY_S, etc...
有 IT_OXYC 或 OXY_SN 或 OXY_S 等...
(x LIKE 'IT_OXYC' OR x LIKE 'IOXY_SN' OR x LIKE 'OXY_S' OR ...)
(x LIKE ' IT_OXYC' OR x LIKE ' IOXY_SN' OR x LIKE ' OXY_S' OR ...)
and DO have either OXYC_SNEG, or OXYC_PNEG,
并且确实有 OXYC_SNEG 或 OXYC_PNEG，
AND (x LIKE 'OXYC_SNEG' OR x LIKE 'OXYC_PNEG')
AND (x LIKE ' OXYC_SNEG' 或 x LIKE ' OXYC_PNEG')
Putting it all together:
把它们放在一起：
SELECT *
FROM accountsnew
WHERE NOT ([Panels] LIKE '*IT_OXYC*' OR
[Panels] LIKE '*IOXY_SN*' OR
[Panels] LIKE '*OXY_S*' OR ...)
AND ([Panels] LIKE '*OXYC_SNEG*' OR
[Panels] LIKE '*OXYC_PNEG*')
回答by Kyra
Not sure if it makes a difference but I would put in brackets near the end so:
不确定它是否有所不同，但我会在末尾附近加上括号，因此：
WHERE [Panels] not like '*IT_OXYC,*' and [Panels] not like '*OXY_SN,*' and [Panels] not like '*OXY_S,*' and [Panels] not like '*OXY_N,*' and ([Panels] like '*OXYC_SNEG,*' or [Panels] like '*OXYC_PNEG,*');
You have one ORin a line containing only ANDs. It might be seeing one of the ORs as true and thinking the whole line is true
您OR在一行中只包含一个ANDs。它可能将ORs 中的一个视为真的，并认为整行都是真的
回答by roufamatic
please pardon my dear aunt sally: You may need some parentheses to get this to do what you want. I suspect you need to place them at the end like so:
请原谅我亲爱的阿姨莎莉：你可能需要一些括号才能让它做你想做的事。我怀疑你需要像这样把它们放在最后：
SELECT *
FROM accountsnew
WHERE ([Panels] not like '*IT_OXYC,*' and [Panels] not like '*OXY_SN,*' and [Panels] not
like '*OXY_S,*' and [Panels] not like '*OXY_N,*')
and ([Panels] like '*OXYC_SNEG,*' or [Panels] like '*OXYC_PNEG,*');
回答by dkretz
I remember with Access it works better to say
我记得使用 Access 更有效地说
SELECT *
FROM accountsnew
WHERE NOT [Panels] like '*IT_OXYC,*'
and not [Panels] like '*OXY_SN,*'
and not [Panels] like '*OXY_S,*'
etc.
等等。
回答by texpert
some of your records which contain oxyc_sneg and oxcy_pneg, also contain it_oxcy and others....
你的一些包含 oxyc_sneg 和 oxcy_pneg 的记录，也包含 it_oxcy 和其他......
So, when you try to get records, where [Panels] like 'OXYC_SNEG,' or [Panels] like 'OXYC_PNEG,, it also gives you some records which contains both OXYC_SNEG and it_oxcy or OXYC_PNEG and it_oxcy etc...
因此，当您尝试获取记录，其中 [Panels] 像 ' OXYC_SNEG,' 或 [Panels] 像 ' OXYC_PNEG,，它还会为您提供一些包含 OXYC_SNEG 和 it_oxcy 或 OXYC_PNEG 和 it_oxcy 等的记录...
so what can you do here is something like,
所以你能在这里做什么就像，
SELECT *
FROM accountsnew
WHERE { [Panels] like '*OXYC_SNEG,*' and
{[Panels] not like '*IT_OXYC,*' and [Panels] not like '*OXY_SN,*' and [Panels] not like '*OXY_S,*' and [Panels] not like '*OXY_N,*}
}
or
{[Panels] like '*OXYC_PNEG,*' and
{[Panels] not like '*IT_OXYC,*' and [Panels] not like '*OXY_SN,*' and [Panels] not like '*OXY_S,*' and [Panels] not like '*OXY_N,*} }
this should work fine, but just write in proper syntax before trying it out.
这应该可以正常工作，但只需在尝试之前以正确的语法编写。
回答by Jeff
Don't use OR use AND this will work correctly
不要使用或使用，这将正常工作
