SQL Server 2012中带有'AND'运算符的'LIKE'子句 [英] 'LIKE' clause with 'AND' operator in SQL Server 2012

问题描述

我对此线程的要求完全相同. 如何在SQL中将JOIN LIKE与AND运算符一起使用服务器?

I have exactly the same requirement as in this thread. How to use JOIN LIKE with AND Operator in SQL Server?

尽管上网搜索了几个小时，我还是一无所知.

I have been clueless despite searching the net for hours.

我有一个表"Filter"，其中的"Value"列为2个记录，其中"Value1"和"Value2"为varchar值.

I have a table 'Filter' with column 'Value' and 2 records in it as 'Value1' and 'Value2' as varchar values.

Select * From MasterTable
Inner Join Filter ON MasterTable.Name LIKE '%' + Filter.Value + '%'

此查询的意思是:

Select * From MasterTable
Where MasterTable.Name LIKE '%Value1%' OR MasterTable.Name LIKE '%Value2%'

现在如何更改JOIN LIKE，即AND而不是OR?像这样:

Now how can I change JOIN LIKE that means AND not OR? Something like:

Select * From MasterTable
Where MasterTable.Name LIKE '%Value1%' AND MasterTable.Name LIKE '%Value2%'

任何人都可以向我展示一条出路.

Can anyone show me a way out.

推荐答案

您通常可以将"X匹配所有Y"查询重写为"X没有任何不匹配的Y"-换句话说，就是查询Y的倒数.

You can generally rewrite a "X matches all Y" query as "X does not have any Y that do not match" - or in other words a NOT EXISTS query for the inverse of Y.

select *
from MasterTable
where not exists (
    select 1
    from Filter
    where MasterTable.Name not like '%' + Filter.Value + '%'
)

这篇关于SQL Server 2012中带有'AND'运算符的'LIKE'子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！