sql: 喜欢任何 vs 喜欢所有 [英] sql: like any vs like all

问题描述

我不明白为什么有时 LIKE 需要 ANY 而有时它需要 ALL，这让我发疯.我觉得我应该能够在两种情况下都使用 ANY (我试图在括号中的任何正则表达式之后选择记录).

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).

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

For some reason, the first LIKE, with ANY, works just fine - it returns all records with dog chow, pedigree, or beneful.

然而，第二个 LIKE 需要 ALL.否则它不会遗漏零食、用品或湿的记录.但为什么?我觉得这里的任何形式都合适.

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.

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

推荐答案

LIKE ANY 转换为 ORed 条件，但 LIKE ALL 转换为AND:

LIKE ANY translates to ORed condition, but LIKE ALL to 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 对每个非 NULL 行都成立.

If you replace the AND with OR it's like col <> 1 OR col <> 2 which is true for every non-NULL row.

这篇关于sql: 喜欢任何 vs 喜欢所有的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！