sql: 喜欢任何 vs 喜欢所有
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40475982/
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: like any vs like all
提问by Adam
I can't figure out why sometimes LIKE requires ANY and other times it requires ALL, and it's making me crazy. I feel like I should be able to use ANY in both conditions (I'm trying to select records following any of the regex expressions in parentheses).
我不明白为什么有时 LIKE 需要 ANY 有时它需要 ALL，这让我发疯。我觉得我应该能够在两种情况下都使用 ANY （我试图在括号中的任何正则表达式之后选择记录）。
For some reason, the first LIKE, with ANY, works just fine - it returns all records with dog chow, pedigree, or beneful.
出于某种原因，第一个 LIKE 和 ANY 一起工作得很好——它返回所有带有狗食、血统或有益的记录。
The second LIKE, however, requires ALL. Otherwise it won't leave out records with treat, supplies or wet. But why? I feel like ANY is the appropriate form here.
然而，第二个 LIKE 需要 ALL。否则它不会遗漏零食、用品或湿的记录。但为什么？我觉得这里的任何形式都是合适的。
where dsc_item like any ('%DOG CHOW%','%PEDIGREE%','%BENEFUL%')
and dsc_comm not like all ('%TREATS%','%SUPPLIES%', '%WET%')
回答by dnoeth
LIKE ANYtranslates to ORed condition, but LIKE ALLto AND:
LIKE ANY转换为ORed 条件，但转换LIKE ALL为AND：
where
( dsc_item like '%DOG CHOW%'
OR dsc_item like '%PEDIGREE%','%BENEFUL%'
)
and
( dsc_comm not like '%TREATS%'
AND dsc_comm not like '%SUPPLIES%'
AND dsc_comm not like '%WET%'
)
If you replace the ANDwith ORit's like col <> 1 OR col <> 2which is true for every non-NULL row.
如果你AND用OR它替换它就像col <> 1 OR col <> 2对于每个非空行都是真的。
回答by David ???? Markovitz
like any similar to = any
like all similar to = all
not like any similar to <> any
not like all similar to <> all
select 'My name is Inigo Montoya, you killed mhy father, prepare to die!' as str
,case when str like any ('%Inigo%','%Donald%' ,'%Hillary%') then 1 else 0 end -- 1
,case when str like any ('%Adam%' ,'%Donald%' ,'%Hillary%') then 1 else 0 end -- 0
,case when str like all ('%Inigo%','%Montoya%','%father%') then 1 else 0 end -- 1
,case when str like all ('%Inigo%','%Montoya%','%mother%') then 1 else 0 end -- 0
,case when str not like any ('%Inigo%','%Montoya%','%mother%') then 1 else 0 end -- 1
,case when str not like any ('%Inigo%','%Montoya%','%father%') then 1 else 0 end -- 0
,case when str not like all ('%Adam%' ,'%Donald%' ,'%Hillary%') then 1 else 0 end -- 1
,case when str not like all ('%Inigo%','%Donald%' ,'%Hillary%') then 1 else 0 end -- 0
;
