SQL 查询 - 在 UNION 中使用 Order By
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/213851/
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
SQL Query - Using Order By in UNION
提问by Curtis Inderwiesche
How can one programmatically sort a union query when pulling data from two tables? For example,
从两个表中提取数据时，如何以编程方式对联合查询进行排序？例如，
SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY table2.field1
Throws an exception
抛出异常
Note: this is being attempted on MS Access Jet database engine
注意：这是在 MS Access Jet 数据库引擎上尝试的
回答by ajgreyling
Sometimes you need to have the ORDER BYin each of the sections that need to be combined with UNION.
有时您需要ORDER BY在需要与UNION.
In this case
在这种情况下
SELECT * FROM
(
SELECT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1
UNION ALL
SELECT * FROM
(
SELECT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2
回答by Anne Porosoff
SELECT field1 FROM table1
UNION
SELECT field1 FROM table2
ORDER BY field1
回答by Anson Smith
I think this does a good job of explaining.
我认为这很好地解释了。
The following is a UNION query that uses an ORDER BY clause:
以下是使用 ORDER BY 子句的 UNION 查询：
select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
UNION
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;
Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set.
由于两个“select”语句之间的列名不同，因此通过它们在结果集中的位置来引用 ORDER BY 子句中的列更为有利。
In this example, we've sorted the results by supplier_name/ company_namein ascending order, as denoted by the "ORDER BY 2".
在此示例中，我们已按supplier_name/company_name按升序对结果进行排序，如“ORDER BY 2”所示。
The supplier_name/ company_namefields are in position #2 in the
result set.
的supplier_name/company_name字段是在结果集中的位置＃2。
Taken from here: http://www.techonthenet.com/sql/union.php
回答by Ian Boyd
Using a concrete example:
使用一个具体的例子：
SELECT name FROM Folders ORDER BY name
UNION
SELECT name FROM Files ORDER BY name
Files:
文件：
name
=============================
RTS.exe
thiny1.etl
thing2.elt
f.txt
tcpdump_trial_license (1).zip
Folders:
文件夹：
name
============================
Contacts
Desktop
Downloads
Links
Favorites
My Documents
Desired Output:(results of first select first, i.e. folders first)
所需输出：（先选择的结果，即先选择文件夹）
Contacts
Desktop
Downloads
Favorites
Links
My Documents
f.txt
RTMS.exe
tcpdump_trial_license (1).zip
thiny1.etl
thing2.elt
SQL to achieve the desired results:
达到预期结果的SQL：
SELECT name
FROM (
SELECT 1 AS rank, name FROM Folders
UNION
SELECT 2 AS rank, name FROM Files) dt
ORDER BY rank, name
回答by Todd Price
Here's an example from Northwind 2007:
这是 Northwind 2007 的一个例子：
SELECT [Product ID], [Order Date], [Company Name], [Transaction], [Quantity]
FROM [Product Orders]
UNION SELECT [Product ID], [Creation Date], [Company Name], [Transaction], [Quantity]
FROM [Product Purchases]
ORDER BY [Order Date] DESC;
The ORDER BY clause just needs to be the last statement, after you've done all your unioning. You can union several sets together, then put an ORDER BY clause after the last set.
在完成所有联合之后，ORDER BY 子句只需作为最后一个语句即可。您可以将多个集合联合在一起，然后在最后一个集合之后放置一个 ORDER BY 子句。
回答by Nick
(SELECT table1.field1 FROM table1
UNION
SELECT table2.field1 FROM table2) ORDER BY field1
Work? Remember think sets. Get the set you want using a union and then perform your operations on it.
工作？记住思考集。使用联合获取您想要的集合，然后对其执行操作。
回答by JohnMcG
SELECT table1Column1 as col1,table1Column2 as col2
FROM table1
UNION
( SELECT table2Column1 as col1, table1Column2 as col2
FROM table2
)
ORDER BY col1 ASC
回答by MJ Latifi
SELECT field1
FROM ( SELECT field1 FROM table1
UNION
SELECT field1 FROM table2
) AS TBL
ORDER BY TBL.field1
(use ALIAS)
（使用别名）
回答by tlang
This is the stupidest thing I've ever seen, but it works, and you can't argue with results.
这是我见过的最愚蠢的事情，但它有效，而且你无法与结果争论。
SELECT *
FROM (
SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY table2.field1
) derivedTable
The interior of the derived table will not execute on its own, but as a derived table works perfectly fine. I've tried this on SS 2000, SS 2005, SS 2008 R2, and all three work.
派生表的内部不会自行执行，但作为派生表工作得很好。我已经在 SS 2000、SS 2005、SS 2008 R2 上尝试过这个，而且这三个都可以。
回答by Prayut Parsekar
This is how it is done
这是如何完成的
select * from
(select top 100 percent pointx, pointy from point
where pointtype = 1
order by pointy) A
union all
select * from
(select top 100 percent pointx, pointy from point
where pointtype = 2
order by pointy desc) B
