MySQL 数据库。如何使用自连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16701920/
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
MySql. How to use Self Join
提问by hank99
I need to use Self Join on this table.
我需要在这张桌子上使用 Self Join。
+------------+------+--------+
| Country | Rank | Year |
+------------+------+--------+
|France | 55 | 2000 |
+------------+------+--------+
|Canada | 30 | 2000 |
+------------+------+--------+
|Liberia | 59 | 2001 |
+------------+------+--------+
|Turkey | 78 | 2000 |
+------------+------+--------+
|Japan | 65 | 2003 |
+------------+------+--------+
|Romania | 107 | 2001 |
+------------+------+--------+
I need to use self join to get what countries has the same year as Turkey. Display the Country and year only.
我需要使用 self join 来获取与土耳其同年的国家/地区。仅显示国家和年份。
This is what I am trying to do.
这就是我想要做的。
SELECT DISTINCT a.Country, a.Year
FROM table1 AS a, table1 AS b
WHERE a.Year=b.Year and a.Country='Turkey';
^ googled self join, and made it.
^ 用谷歌搜索自我加入，并成功了。
I am getting only Turkey. What am I doing wrong?
我只得到土耳其。我究竟做错了什么？
回答by xQbert
You're so close!
你离得那么近！
Since you say you're displaying the country and year from A and limiting by A. Countryof Turkey, Turkey is all you're going to see. You either need to change the selects to be B.countryand B.yearor change the where clause to be B.country.
由于您说要显示 A 的国家和年份并限制A. Country土耳其，因此您将看到土耳其。您需要将选择更改为B.country和B.year或将 where 子句更改为B.country。
This is using a cross join which will get slower the more records there are in a table.
这是使用交叉连接，表中的记录越多，速度就越慢。
SELECT DISTINCT b.Country, b.Year
FROM table1 AS a,
table1 AS b
WHERE a.Year=b.Year
and a.Country='Turkey';
could be written as... and would likely have the same execution plan.
可以写成……并且可能有相同的执行计划。
SELECT DISTINCT b.Country, b.Year
FROM table1 AS a
CROSS JOIN table1 AS b
WHERE a.Year=b.Year
and a.Country='Turkey';
OR This uses an INNER JOIN which limits the work the engine must do and doesn't suffer from performance degradation that a cross join would.
或 这使用了一个 INNER JOIN，它限制了引擎必须做的工作，并且不会像交叉连接那样遭受性能下降。
SELECT DISTINCT a.Country, a.Year
FROM table1 AS a
INNER JOIN table1 AS b
on a.Year=b.Year
and b.Country='Turkey';
WHY:
为什么：
Consider what the SQL engine will do when the join occurs A B
考虑当join发生时SQL引擎会做什么 AB
+------------+------+--------+------------+------+--------+
| A.Country | Rank | Year | B.Country | Rank | Year |
+------------+------+--------+------------+------+--------+
|France | 55 | 2000 |France | 55 | 2000 |
+------------+------+--------+------------+------+--------+
|Canada | 30 | 2000 |France | 55 | 2000 |
+------------+------+--------+------------+------+--------+
|Turkey | 78 | 2000 |France | 55 | 2000 |
+------------+------+--------+------------+------+--------+
|France | 55 | 2000 |Canada | 30 | 2000 |
+------------+------+--------+------------+------+--------+
|Canada | 30 | 2000 |Canada | 30 | 2000 |
+------------+------+--------+------------+------+--------+
|Turkey | 78 | 2000 |Canada | 30 | 2000 |
+------------+------+--------+------------+------+--------+
|France | 55 | 2000 |Turkey | 78 | 2000 |
+------------+------+--------+------------+------+--------+
|Canada | 30 | 2000 |Turkey | 78 | 2000 |
+------------+------+--------+------------+------+--------+
|Turkey | 78 | 2000 |Turkey | 78 | 2000 |
+------------+------+--------+------------+------+--------+
So when you said display A.Countryand A.Yearwhere A.Countryis Turkey, you can see all it can return is Turkey (due to the distinct only 1 record)
所以当你说显示A.Country和土耳其A.Year在哪里时A.Country，你可以看到它可以返回的所有是土耳其（由于不同的只有 1 条记录）
But if you do B.Countryis Turkey and display A.Country, you'll get France, Canada and Turkey!
但是如果你做的B.Country是土耳其和展示A.Country，你会得到法国、加拿大和土耳其！
回答by Explosion Pills
Change a.Country = 'Turkey'to b.Country = 'Turkey'
更改a.Country = 'Turkey'为b.Country = 'Turkey'
You have SELECT DISTINCT a.Country, but your condition is a.Country = 'Turkey'. Even if you do get multiple rows, they are filtered by the DISTINCT
你有SELECT DISTINCT a.Country，但你的条件是a.Country = 'Turkey'。即使你得到多行，它们也会被过滤DISTINCT
回答by griffintaur
select distinct country,year from table1 where year=(select year from table
where country='turkey') and country !=turkey;
