MySql的.如何使用自我加入 [英] MySql. How to use Self Join

问题描述

我需要在此表上使用自我联接".

I need to use Self Join on this table.

+------------+------+--------+
| 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.

这就是我想要做的.

SELECT DISTINCT a.Country, a.Year 
FROM table1 AS a, table1 AS b 
WHERE a.Year=b.Year and a.Country='Turkey';

^用Google搜索自我加入并成功加入.

我只得到土耳其.我在做什么错了?

I am getting only Turkey. What am I doing wrong?

推荐答案

您是如此亲密！

因为您说的是显示A的国家和年份，并限制了土耳其的A. Country，所以您将只看到土耳其.您要么需要将selects更改为B.country和B.year，要么将where子句更改为B.country.

Since you say you're displaying the country and year from A and limiting by A. Country of Turkey, Turkey is all you're going to see. You either need to change the selects to be B.country and B.year or change the where clause to be 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';

或 这使用了INNER JOIN，它限制了引擎必须完成的工作，并且不会遭受交叉连接会导致的性能下降.

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.

SELECT DISTINCT a.Country, a.Year 
FROM table1 AS a 
INNER JOIN table1 AS b 
   on a.Year=b.Year 
  and b.Country='Turkey';

为什么:

考虑联接发生时SQL引擎将执行的操作 A B

Consider what the SQL engine will do when the join occurs A B

+------------+------+--------+------------+------+--------+
| 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  |
+------------+------+--------+------------+------+--------+ 

因此，当您说显示A.Country和A.Year时，其中A.Country是土耳其，您可以看到它可以返回的全部是土耳其(由于唯一的1条记录)

So when you said display A.Country and A.Year where A.Country is Turkey, you can see all it can return is Turkey (due to the distinct only 1 record)

但是，如果您选择B.Country是土耳其并显示A.Country，则将获得法国，加拿大和土耳其！

But if you do B.Country is Turkey and display A.Country, you'll get France, Canada and Turkey!

这篇关于MySql的.如何使用自我加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！