你太接近了!
既然你说你正在显示 A 中的国家和年份,并限制为A. Country
土耳其,你所看到的就是土耳其。您需要将选择更改为B.country
and B.year
或将 where 子句更改为B.country
.
这是使用交叉联接,表中的记录越多,交叉联接的速度就会越慢。
SELECT DISTINCT b.Country, b.Year
FROM table1 AS a,
table1 AS b
WHERE a.Year=b.Year
and a.Country='Turkey';
可以写成...并且可能具有相同的执行计划。
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,它限制了引擎必须执行的工作,并且不会遭受交叉连接所带来的性能下降。
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:
考虑当连接发生时 SQL 引擎将做什么
甲乙
+------------+------+--------+------------+------+--------+
| 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
and A.Year
where A.Country
是土耳其,可以看到它能返回的都是土耳其(由于不同只有1条记录)
但如果你这样做B.Country
是土耳其并显示A.Country
,你会得到法国、加拿大和土耳其!