如何从多个表中选择不同的值 [英] how to select distinct value from multiple tables

问题描述

我需要从3个表中获取不同的值。

当我执行此代码时：

 从a，b，c 
选择DISTINCT（城市） 

一个错误，说我的列'城市'是不明确的。

也试过这个：

 从NATURAL JOIN选择DISTINCT（城市）b NATURAL JOIN c 
  

让我告诉你我想做什么的例子：

 表A表B表C 
 id |城市ID |城市ID | city 
 1 |克拉科夫1 |巴黎1 | Paris 
 2 |巴黎2 |伦敦2 |克拉科夫
 3 |巴黎3 |奥斯陆
 4 |罗马
  

我需要得到这样的结果

 结果
 city 
 ---- 
克拉科夫
巴黎
罗马
伦敦
奥斯陆
  

城市的顺序对我来说不重要我只需要拥有它们，只有每个城市的一个表示。

任何想法？我想在 JOIN 中使用 id ，但没有连接，所以我不能使用。 UNION 关键字将返回 unique code>结果列表中的记录。当指定 ALL （ UNION ALL ）时，结果集将保持重复，OP不需要。

 选择城市FROM tableA 
 UNION 
选择城市FROM tableB 
 UNION 
选择城市FROM tableC $ b b  





• SQLFiddle演示

结果

 ╔════════╗
║CITY║
╠═══════╣╣
║克拉科夫║
║巴黎║
║罗马║
║伦敦║
║奥斯陆║
╚════════╝
  

I need to get distinct values from 3 tables.

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's in the JOIN but there are not connected so I can't use that.

解决方案

The UNION keyword will return unique records on the result list. When specifying ALL (UNION ALL) will keep duplicates on the result set, which the OP don't want.

SELECT city FROM tableA
UNION
SELECT city FROM tableB
UNION
SELECT city FROM tableC

• SQLFiddle Demo

RESULT

╔════════╗
║  CITY  ║
╠════════╣
║ Krakow ║
║ Paris  ║
║ Rome   ║
║ London ║
║ Oslo   ║
╚════════╝

这篇关于如何从多个表中选择不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！