SQL SQL中内连接中的“Like”运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23276344/
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
"Like" operator in inner join in SQL
提问by japem
Using Sequel Pro, I have these two tables:
使用 Sequel Pro，我有这两个表：
Table1
Name Year x y
John Smith 2010 10 12
Adam Jones 2010 8 13
John Smith 2011 7 15
Adam Jones 2011 9 14
etc.
and
和
Table2
Name Year z
Smith John Smith John 2010 27
Jones Adam Jones Adam 2010 25
Smith John Smith John 2011 29
Jones Adam Jones Adam 2011 21
etc.
Basically, the names in Table2 are the same only with the last name and first name switched, then repeated once. So the Names in Table1 are found in the names of Table2 ("John Smith" is found in "Smith John SmithJohn"). I want to perform an inner join and connect the z value of Table2 to the other values of Table1 and get something like this:
基本上，表2中的名称相同，只是姓和名互换，然后重复一次。因此，表 1 中的名称可以在表 2 的名称中找到（“John Smith”可以在“Smith John SmithJohn”中找到）。我想执行一个内部连接并将 Table2 的 z 值连接到 Table1 的其他值并得到如下内容：
Name x y z
John Smith 10 12 27
Adam Jones 8 13 25
So to do that, I ran this query:
为此，我运行了以下查询：
Select Table1.*, Table2.z
From Table1
Inner join Table2
On Table1.Name like "%Table2.Name%" and Table1.Year=Table2.Year
But I got this as the output:
但我得到了这个作为输出：
Name Year x y z
And that's it. I got the headings, but no rows. I don't know what I'm doing wrong... I suspect it probably has to do with the way I'm using the like operator but I don't know. Any help would be much appreciated.
就是这样。我得到了标题，但没有行。我不知道我做错了什么……我怀疑这可能与我使用 like 运算符的方式有关，但我不知道。任何帮助将非常感激。
回答by Joachim Isaksson
A bit of an odd data model aside, you've turned the tables around in the LIKEpart (table1.name should be a part of table2.name, not the other way around), and you need to add the percents to the value, not the nameof the field, that means not quoting the name;
除了一些奇怪的数据模型之外，您已经在LIKE部分中翻转了表格（table1.name 应该是 table2.name 的一部分，而不是相反），并且您需要将百分比添加到value 中，不是字段的名称，这意味着不引用名称；
SELECT table1.*, table2.z
FROM table1
INNER JOIN table2
ON table2.name LIKE CONCAT('%', table1.name, '%')
AND table1.year = table2.year
回答by Tomas Pastircak
Your query is incorrect, you are saying that the content of the column should be like abcdTable2.Nameefgh. This would be correct:
您的查询不正确，您是说该列的内容应该类似于abcdTable2.Nameefgh。这是正确的：
Select Table1.*, Table2.z
From Table1
Inner join Table2
On Table1.Name like "%" + Table2.Name+ "%" and Table1.Year=Table2.Year
This query will be quite slow for bigger table, but I'm afraid that if you are joining on a name only, the table can't really be bigger as you'll have duplicates quite soon.
对于更大的表，这个查询会很慢，但我担心如果你只加入一个名字，这个表不会真的更大，因为你很快就会有重复项。
回答by Reza
try this:
尝试这个：
Select Table1.*, Table2.z
From Table1
Inner join Table2
On Table1.Name like Concat('%',Table2.Name,'%') and Table1.Year=Table2.Year
in your query it will search for string containing Table2.Name(it is like constant)
在您的查询中，它将搜索包含字符串Table2.Name（就像常量）
as a suggestion joining on names is very very bad, what if you have 2 person with the same name??! So you need to have a primary and foreign key for this.
作为加入名字的建议非常非常糟糕，如果你有两个同名的人怎么办？？！所以你需要有一个主键和外键。
