“喜欢"SQL中内连接中的运算符 [英] "Like" operator in inner join in SQL

问题描述

使用 Sequel Pro，我有这两个表:

Using Sequel Pro, I have these two tables:

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.

和

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.

表2中的名字基本上是一样的，只是姓和名互换了，然后重复了一次.因此，表 1 中的名称可以在表 2 的名称中找到(John Smith"可以在Smith John Smith John"中找到).我想执行一个内部连接并将 Table2 的 z 值连接到 Table1 的其他值并得到如下内容:

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 Smith John"). 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:

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

就是这样.我得到了标题，但没有行.我不知道我做错了什么......我怀疑这可能与我使用 like 运算符的方式有关，但我不知道.任何帮助将不胜感激.

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 部分的表格(table1.name 应该是table2.name 的一部分，而不是相反)，并且您需要将百分比添加到 value，而不是字段的 name，这意味着不引用名字;

A bit of an odd data model aside, you've turned the tables around in the LIKE part (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 name of the field, that means not quoting the name;

SELECT table1.*, table2.z
FROM table1
INNER JOIN table2
  ON table2.name LIKE CONCAT('%', table1.name, '%') 
 AND table1.year = table2.year

用于测试的 SQLfiddle.

这篇关于“喜欢"SQL中内连接中的运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！