在INNER JOIN中使用LIKE子句 [英] Use a LIKE clause in part of an INNER JOIN

问题描述

在构建存储过程/查询时，可以/应该使用LIKE条件作为INNER JOIN的一部分吗？我不知道我在问正确的事情，所以让我解释一下。

Can/Should I use a LIKE criteria as part of an INNER JOIN when building a stored procedure/query? I'm not sure I'm asking the right thing, so let me explain.

我正在创建一个将要列出关键字的过程在包含文本的列中进行搜索。如果我坐在控制台，我会执行它：

I'm creating a procedure that is going to take a list of keywords to be searched for in a column that contains text. If I was sitting at the console, I'd execute it as such:

SELECT Id, Name, Description
  FROM dbo.Card
 WHERE Description LIKE '%warrior%' 
       OR
       Description LIKE '%fiend%' 
       OR 
       Description LIKE '%damage%'

但是，在一个存储过程中，我选择了一些要做强类型列表解析的技巧是将列表解析成表变量/临时表，将其转换为正确的类型，然后对我的最终结果集中的表执行INNER JOIN。当向过程发送一个整数ID的列表时，这很好。我收到了一个如下所示的最终查询：

But a trick I picked up a little while go to do "strongly typed" list parsing in a stored procedure is to parse the list into a table variable/temporary table, converting it to the proper type and then doing an INNER JOIN against that table in my final result set. This works great when sending say a list of integer IDs to the procedure. I wind up having a final query that looks like this:

SELECT Id, Name, Description
  FROM dbo.Card
       INNER JOIN @tblExclusiveCard ON dbo.Card.Id = @tblExclusiveCard.CardId

我想使用这个技巧与一个字符串列表。但是，由于我正在寻找一个特定的关键字，我将使用LIKE子句。所以理想情况下，我想我最终的查询如下所示：

I want to use this trick with a list of strings. But since I'm looking for a particular keyword, I am going to use the LIKE clause. So ideally I'm thinking I'd have my final query look like this:

SELECT Id, Name, Description
  FROM dbo.Card
       INNER JOIN @tblKeyword ON dbo.Card.Description LIKE '%' + @tblKeyword.Value + '%'

这是可能/推荐吗？

有没有更好的方法来做这样的事情？

Is there a better way to do something like this?

我将通配符放在条款两端的原因是因为有archfiend，beast-warrior，direct-damage 和战斗伤害术语在卡片文本中使用。

The reason I'm putting wildcards on both ends of the clause is because there are "archfiend", "beast-warrior", "direct-damage" and "battle-damage" terms that are used in the card texts.

我得到的印象是，根据性能，我可以使用查询我指定或使用全文关键字搜索来完成相同的任务？

I'm getting the impression that depending on the performance, I can either use the query I specified or use a full-text keyword search to accomplish the same task?

除了让服务器在我想要文本搜索的字段上进行文本索引之外，还有什么我需要做的吗？

Other than having the server do a text index on the fields I want to text search, is there anything else I need to do?

推荐答案

您的第一个查询将工作，但将需要完整的表扫描，因为该列上的任何索引将被忽略。您还需要做一些动态SQL来生成所有的LIKE子句。

Your first query will work but will require a full table scan because any index on that column will be ignored. You will also have to do some dynamic SQL to generate all your LIKE clauses.

如果使用SQL Server或查看其中一个 Lucene 实现。 Joel最近谈到他的成功。

Try a full text search if your using SQL Server or check out one of the Lucene implementations. Joel talked about his success with it recently.

这篇关于在INNER JOIN中使用LIKE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！