sql: like any vs like all

Question

我搞不懂为什么有时候LIKE需要任何东西，而另一些时候它需要所有东西，这让我发疯。我觉得我应该能够在这两个条件下使用ANY (我正在尝试选择括号中任何regex表达式后面的记录)。

出于某种原因，第一个LIKE和ANY都工作得很好--它返回所有带有狗食、血统或beneful的记录。

然而，第二个LIKE需要所有。否则，它不会遗漏关于款待、补给或湿的记录。但是为什么呢？我觉得ANY是这里合适的形式。

where dsc_item like any ('%DOG CHOW%','%PEDIGREE%','%BENEFUL%')
and dsc_comm not like all ('%TREATS%','%SUPPLIES%', '%WET%')

Answer 1

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%'
 )

如果将AND替换为OR，则类似于col <> 1 OR col <> 2，这对于每个非空行都是真的。

Answer 2

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
;