MySQL 如何从多个表中选择不同的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15310782/
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
how to select distinct value from multiple tables
提问by user123_456
I need to get distinct values from 3 tables.
我需要从 3 个表中获取不同的值。
When I perform this code:
当我执行此代码时：
select DISTINCT(city) from a,b,c
I get an error which says that my column 'city' is ambiguous.
我收到一个错误，说我的“城市”列不明确。
Also I have tried this:
我也试过这个：
select DISTINCT(city) from a NATURAL JOIN b NATURAL JOIN c
With this code I receive nothing from my tables.
使用此代码，我从我的表中没有收到任何信息。
Let me show you on the example of what I am trying to do:
让我向您展示我正在尝试做的示例：
TABLE A TABLE B TABLE C
id | city id | city id | city
1 | Krakow 1 | Paris 1 | Paris
2 | Paris 2 | London 2 | Krakow
3 | Paris 3 | Oslo
4 | Rome
And I need to get result like this
我需要得到这样的结果
RESULTS
city
----
Krakow
Paris
Rome
London
Oslo
Order of the cities is not important to me I just need to have them all, and there should be only one representation of each city.
城市的顺序对我来说并不重要，我只需要拥有它们，并且每个城市应该只有一个表示。
Any idea? I was thinking to use id'sin the JOINbut there are not connected so I can't use that.
任何的想法？我想使用id's的JOIN，但有没有连接，所以我不能使用。
回答by John Woo
The UNIONkeyword will return uniquerecords on the result list. When specifying ALL(UNION ALL) will keep duplicates on the result set, which the OP don't want.
该UNION关键字将返回unique结果列表中的记录。指定ALL( UNION ALL) 将在结果集上保留重复项，这是 OP 不想要的。
SELECT city FROM tableA
UNION
SELECT city FROM tableB
UNION
SELECT city FROM tableC
RESULT
结果
╔════════╗
║ CITY ║
╠════════╣
║ Krakow ║
║ Paris ║
║ Rome ║
║ London ║
║ Oslo ║
╚════════╝
回答by James C
SELECT city FROM A
UNION DISTINCT
SELECT city FROM B
UNION DISTINCT
SELECT city FROM C
