SQL 在 INNER JOIN 的一部分中使用 LIKE 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20484/
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
Use a LIKE clause in part of an INNER JOIN
提问by Dillie-O
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.
在构建存储过程/查询时，我可以/应该使用 LIKE 条件作为 INNER JOIN 的一部分吗？我不确定我问的对不对，所以让我解释一下。
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%'
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:
但是我在存储过程中进行“强类型”列表解析时学到的一个技巧是将列表解析为表变量/临时表，将其转换为正确的类型，然后对该表执行 INNER JOIN在我的最终结果集中。这在向程序发送整数 ID 列表时非常有效。我最终得到了一个如下所示的最终查询：
SELECT Id, Name, Description
FROM dbo.Card
INNER JOIN @tblExclusiveCard ON dbo.Card.Id = @tblExclusiveCard.CardId
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:
我想将此技巧与字符串列表一起使用。但由于我要查找特定关键字，因此我将使用 LIKE 子句。所以理想情况下，我想我的最终查询是这样的：
SELECT Id, Name, Description
FROM dbo.Card
INNER JOIN @tblKeyword ON dbo.Card.Description LIKE '%' + @tblKeyword.Value + '%'
Is this possible/recommended?
这可能/推荐吗？
Is there a better way to do something like this?
有没有更好的方法来做这样的事情？
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.
我将通配符放在条款的两端的原因是因为在卡片文本中使用了“archfiend”、“beast-warrior”、“direct-damage”和“battle-damage”术语。
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?
除了让服务器在我想要文本搜索的字段上做文本索引之外，还有什么我需要做的吗？
采纳答案by jason saldo
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 来生成所有 LIKE 子句。
Try a full text search if your using SQL Server or check out one of the Luceneimplementations. Joel talked about his success with it recently.
如果您使用 SQL Server 或查看Lucene实现之一，请尝试全文搜索。乔尔最近谈到了他的成功。
回答by John
Try this
尝试这个
select * from Table_1 a
left join Table_2 b on b.type LIKE '%' + a.type + '%'
This practice is not ideal. Use with caution.
这种做法并不理想。谨慎使用。
回答by jason saldo
@Dillie-O
How big is this table?
What is the data type of Description field?
@Dillie-O
这张桌子有多大？
描述字段的数据类型是什么？
If either are small a full text search will be overkill.
如果其中任何一个都很小，则全文搜索将是矫枉过正。
@Dillie-O
Maybe not the answer you where looking for but I would advocate a schema change...
@Dillie-O
也许不是您要寻找的答案，但我会提倡架构更改...
proposed schema:
提议的架构：
create table name(
nameID identity / int
,name varchar(50))
create table description(
descID identity / int
,desc varchar(50)) --something reasonable and to make the most of it alwase lower case your values
create table nameDescJunc(
nameID int
,descID int)
This will let you use index's without have to implement a bolt on solution, and keeps your data atomic.
这将让您使用索引而不必实施螺栓解决方案，并保持您的数据原子。
related: Recommended SQL database design for tags or tagging
回答by onedaywhen
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
我在存储过程中进行“强类型”列表解析时学到的一个技巧是将列表解析为表变量/临时表
I think what you might be alluding to here is to put the keywords to include into a table then use relational divisionto find matches (could also use another table for words to exclude). For a worked example in SQL see Keyword Searches by Joe Celko.
我认为您在这里可能暗示的是将要包含的关键字放入表格中，然后使用关系除法来查找匹配项（也可以使用另一个表格来排除要排除的单词）。有关 SQL 中的有效示例，请参阅Joe Celko 的关键字搜索。
回答by Nick Berardi
It seems like you are looking for full-text search. Because you want to query a set of keywords against the card description and find any hits? Correct?
看起来您正在寻找全文搜索。因为您想根据卡片描述查询一组关键字并找到任何匹配项？正确的？
回答by Darren Kopp
Personally, I have done it before, and it has worked out well for me. The only issues i could see is possibly issues with an unindexed column, but i think you would have the same issue with a where clause.
就我个人而言，我以前做过，而且对我来说效果很好。我能看到的唯一问题可能是未编入索引的列有问题，但我认为您在使用 where 子句时也会遇到同样的问题。
My advice to you is just look at the execution plans between the two. I'm sure that it will differ which one is better depending on the situation, just like all good programming problems.
我给你的建议是看看两者之间的执行计划。我确信它会因情况而异，哪个更好，就像所有好的编程问题一样。
回答by Manoj
try it...
尝试一下...
select * from table11 a inner join table2 b on b.id like (select '%'+a.id+'%') where a.city='abc'.
Its works for me.:-)
它对我有用。:-)
回答by Chris Miller
Performance will be depend on the actual server than you use, and on the schema of the data, and the amount of data. With current versions of MS SQL Server, that query should run just fine (MS SQL Server 7.0 had issues with that syntax, but it was addressed in SP2).
性能将取决于您使用的实际服务器、数据架构和数据量。使用当前版本的 MS SQL Server，该查询应该可以正常运行（MS SQL Server 7.0 的语法存在问题，但已在 SP2 中解决）。
Have you run that code through a profiler? If the performance is fast enough and the data has the appropriate indexes in place, you should be all set.
您是否通过分析器运行该代码？如果性能足够快并且数据具有适当的索引，那么您应该一切准备就绪。
回答by Chris Miller
Try this;
尝试这个;
SELECT Id, Name, Description
FROM dbo.Card
INNER JOIN @tblKeyword ON dbo.Card.Description LIKE '%' +
CONCAT(CONCAT('%',@tblKeyword.Value),'%') + '%'
回答by SQLMenace
LIKE '%fiend%' will never use an seek, LIKE 'fiend%' will. Simply a wildcard search is not sargable
LIKE '%fiend%' 永远不会使用搜索，LIKE 'fiend%' 会。简单的通配符搜索不是 sargable
