SQL查询-在联合中使用ORDER BY

有时候你需要 ORDER BY 在需要与 UNION .

在这种情况下

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

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

我认为这解释得很好。

以下是使用ORDER BY子句的联合查询:

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;

由于两个“select”语句的列名称不同,因此按列在结果集中的位置引用ORDER BY子句中的列更为有利。

在这个例子中,我们已经按照 supplier_name / company_name 按升序排列,由“2”表示。

这个 供应商名称 / 公司名称 字段在中的位置2 结果集。

从这里取: http://www.techonthenet.com/sql/union.php

使用一个具体的例子:

SELECT name FROM Folders ORDER BY name
UNION
SELECT name FROM Files ORDER BY name

文件夹:

name
=============================
RTS.exe
thiny1.etl
thing2.elt
f.txt
tcpdump_trial_license (1).zip

文件夹:

name
============================
Contacts
Desktop
Downloads
Links
Favorites
My Documents

期望输出: (先选择结果,即先选择文件夹)

Contacts
Desktop
Downloads
Favorites
Links
My Documents
f.txt
RTMS.exe
tcpdump_trial_license (1).zip
thiny1.etl
thing2.elt

SQL以实现所需的结果:

SELECT name 
FROM (
    SELECT 1 AS rank, name FROM Folders
    UNION 
    SELECT 2 AS rank, name FROM Files) dt
ORDER BY rank, name

下面是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;

ORDERBY子句只需要在您完成所有的联合之后成为最后一条语句。您可以将多个集合合并在一起,然后在最后一个集合之后放置order by子句。

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

工作?记住思维定势。使用union获取所需的集合,然后对其执行操作。

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

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

(使用别名)

这是我见过的最愚蠢的事情,但它起作用了,你不能和结果争论。

SELECT *
FROM (
    SELECT table1.field1 FROM table1 ORDER BY table1.field1
    UNION
    SELECT table2.field1 FROM table2 ORDER BY table2.field1
) derivedTable

派生表的内部将不会单独执行,但作为派生表,它工作得非常好。我在SS 2000、SS 2005、SS 2008 R2和这三个版本上都试过。

就是这样做的

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

浏览这个评论部分,我遇到了两种不同的模式来回答这个问题。不幸的是,对于SQL 2012,第二种模式不起作用,所以这里是我的“解决方法”

按公用列排序

这是最容易遇到的情况。正如许多用户指出的,您真正需要做的就是添加 Order By 在查询结束时

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

或

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

按不同列排序

这就是它真正变得棘手的地方。使用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

根据评论中的建议,我尝试了这个

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

这段代码确实编译了,但是 DUMMY_ALIAS1 和 DUMMY_ALIAS2 重写 按顺序 成立于 Select 使此不可用的语句。

我能想到的唯一有效的解决方案是不使用联合,而是让查询单独运行,然后处理它们。所以基本上,不使用 Union 当你想的时候 按顺序

通过单独使用ORDER,每个子集都得到了ORDER,但不是整个集合,这是您希望将两个表组合在一起的。

你应该用这样的东西 一 有序集:

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

第二个表不能在 ORDER BY 条款。

所以…

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

不引发异常

如果需要保持内部排序:

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

(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

试试这个。这对我很有用。

对于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