SQL 如何在一个查询中通过排序从 2 个表中选择不同的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7584144/
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 values from 2 tables with sort in one query?
提问by ali
I have
我有
- table1 :
country,theOrderColumn1 - table2 :
country,theOrderColumn2
- 表1：
country，theOrderColumn1 - 表2：
country，theOrderColumn2
I want to join DISTINCT countryfrom these two SELECT statements:
我想country从这两个 SELECT 语句中加入 DISTINCT ：
SELECT DISTINCT `country` FROM `table1` ORDER BY `theOrderColumn1`
and
和
SELECT DISTINCT `country` FROM `table2` ORDER BY `theOrderColumn2`
Example:
例子：
table1 (country, theOrderColumn1): (uk, 1), (usa, 2)table2 (country, theOrderColumn2): (france, 1), (uk, 2)
table1 (country, theOrderColumn1): (uk, 1), (usa, 2)table2 (country, theOrderColumn2): (france, 1), (uk, 2)
I want this result:
我想要这个结果：
france
uk
usa
回答by RedFilter
select distinct country
from (
select country, theOrderColumn from table1
union all
select country, theOrderColumn from table2
) a
order by theOrderColumn
回答by Icarus
select country, theOrderColumn from (
select distinct t1.country as country, t1.theOrderColumn as theOrderColumn from table t1
union
select distinct t2.country as country, t2.theOrderColumn as theOrderColumn from table t2) t3
order by theOrderColumn
回答by Tom Mac
select a.country,a.theOrderColumn
(
select country,theOrderColumn
from table1
union
select country,theOrderColumn
from table2
) a
order by a.theOrderColumn
Though you will get duplicates if theOrderColumn is different in table 1 and table 2.
尽管如果表 1 和表 2 中的 OrderColumn 不同，您将得到重复项。
回答by Robert Lowe
It depends on what you are wanting and how you want to join the two tables together. If you are joining based on "theOrderColumn", then the query would be
这取决于您想要什么以及如何将两个表连接在一起。如果您基于“theOrderColumn”加入，那么查询将是
SELECT DISTINCT country
FROM table1
JOIN table2 ON table1.theOrderColumn = table2.theOrderColumn
ORDER BY theOrderColumn
If you want to join across country (which wouldn't make sense as the country would be identical in both tables) then you could swap out "country" in the join clause.
如果您想跨国家/地区加入（这没有意义，因为两个表中的国家/地区都是相同的），那么您可以在 join 子句中换出“国家/地区”。
Also, depending on your SQL dialect spoken by your DBMS, your mileage may vary with the above query. Can you clarify more of what you are after?
此外，根据您的 DBMS 所说的 SQL 方言，您的里程可能因上述查询而异。你能澄清更多你所追求的吗？
回答by Xavi López
If you want to preserve order given by theOrderColumn1and theOrderColumn2at the same time, you can use the column index to specify the ORDER BYcolumn.
如果要同时保留由theOrderColumn1和给出的顺序theOrderColumn2，可以使用列索引来指定ORDER BY列。
SELECT distinct country FROM(
(SELECT country as c, theOrderColumn1 as d from table1
UNION
SELECT country as c, theOrderColumn2 as d from table2
)
order by 2
)
Take a look at the answers to this question: SQL Query - Using Order By in UNION
看看这个问题的答案：SQL Query - Using Order By in UNION
