SQL查询——使用联合的顺序

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数据库引擎上尝试的

16 个解决方案

#1

Sometimes you need to have the ORDER BY in each of the sections that need to be combined with UNION.

有时，您需要在每一个需要与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

#2

SELECT field1 FROM table1
UNION
SELECT field1 FROM table2
ORDER BY field1

#3

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_name in ascending order, as denoted by the "ORDER BY 2".

在本例中，我们按supplier_name / company_name按升序排序，如“order by 2”所示。

The supplier_name / company_name fields are in position #2 in the result set.

supplier_name / company_name字段位于结果集中的位置2。

Taken from here: http://www.techonthenet.com/sql/union.php

来自:http://www.techonthenet.com/sql/union.php

#4

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

#5

Here's an example from Northwind 2007:

这是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子句。

#6

(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.

工作吗?记得想集。使用union获取您想要的集合，然后对其执行操作。

#7

SELECT table1Column1 as col1,table1Column2 as col2
    FROM table1
UNION
(    SELECT table2Column1 as col1, table1Column2 as col2
         FROM table2
)
ORDER BY col1 ASC

#8

SELECT field1
FROM ( SELECT field1 FROM table1
       UNION
       SELECT field1 FROM table2
     ) AS TBL
ORDER BY TBL.field1

(use ALIAS)

(用别名)

#9

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上都试过了，这三个都是。

#10

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

#11

Browsing this comment section I came accross two different patterns answering the question. Sadly for SQL 2012, the second pattern doesn't work, so here's my "work around"

浏览评论部分，我发现有两种不同的模式可以回答这个问题。不幸的是，对于SQL 2012，第二个模式不起作用，所以下面是我的“work around”

---

Order By on a Common Column

This is the easiest case you can encounter. Like many user pointed out, all you really need to do is add an Order By at the end of the query

这是最容易遇到的情况。正如许多用户指出的，您真正需要做的是在查询末尾添加一个Order

SELECT a FROM table1
UNION
SELECT a FROM table2
ORDER BY field1

or

或

SELECT a FROM table1 ORDER BY field1
UNION
SELECT a FROM table2 ORDER BY field1

---

Order By on Different Columns

Here's where it actually gets tricky. Using SQL 2012, I tried the top post and it doesn't work.

这就是它变得棘手的地方。使用SQL 2012，我尝试了top post，但它不起作用。

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

Following the recommandation in the comment I tried this

在评论中命令之后，我尝试了这个

SELECT * FROM 
(
  SELECT TOP 100 PERCENT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT TOP 100 PERCENT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2

This code did compile but the DUMMY_ALIAS1 and DUMMY_ALIAS2 override the Order By established in the Select statement which makes this unusable.

这段代码确实进行了编译，但是mydum_alias1和DUMMY_ALIAS2重写了Select语句中所建立的顺序，这使得这个命令无法使用。

The only solution that I could think of, that worked for me was not using a union and instead making the queries run individually and then dealing with them. So basically, not using a Union when you want to Order By

我能想到的唯一可行的解决方案不是使用联合，而是让查询单独运行，然后处理它们。所以基本上，当你想要订购时不要使用联合

#12

By using order separately each subset gets order, but not the whole set, which is what you would want uniting two tables.

通过分别使用order，每个子集都得到order，而不是整个集合，这就是您希望合并两个表的地方。

You should use something like this to have one ordered set:

您应该使用类似这样的东西来拥有一个有序集:

SELECT TOP (100) PERCENT field1, field2, field3, field4, field5 FROM 
(SELECT table1.field1, table1.field2, table1.field3, table1.field4, table1.field5 FROM table1
UNION ALL 
SELECT table2.field1, table2.field2, table2.field3, table2.field4, table2.field5 FROM  table2) 
AS unitedTables ORDER BY field5 DESC

#13

The second table cannot include the table name in the ORDER BY clause.

第二个表不能在ORDER BY子句中包含表名。

So...

所以…

SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY field1

Does not throw an exception

不抛出异常?

#14

If necessary to keep the inner sorting:

如果需要保持内部排序:

SELECT 1 as type, field1 FROM table1 
UNION 
SELECT 2 as type, field1 FROM table2 
ORDER BY type, field1

#15

(SELECT FIELD1 AS NEWFIELD FROM TABLE1 ORDER BY FIELD1)
UNION
(SELECT FIELD2 FROM TABLE2 ORDER BY FIELD2)
UNION
(SELECT FIELD3 FROM TABLE3 ORDER BY FIELD3) ORDER BY NEWFIELD

Try this. It worked for me.

试试这个。它为我工作。

#16

For Sql Server 2014/2012/Others(Not Checked) :

Sql Server 2014/2012/其他(未勾选):

SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) 
as DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) 
as DUMMY_ALIAS2