SQL 查询 - 在 UNION 中使用 Order By

发布于 2024-07-07 03:38:14 字数 240 浏览 9 评论 0原文

从两个表中提取数据时，如何以编程方式对联合查询进行排序？例如，

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

引发异常

注意：正在 MS Access Jet 数据库引擎上尝试执行此操作

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

暮光沉寂 2024-07-14 03:38:14

有时，您需要在需要与 UNION 组合的每个部分中使用 ORDER BY。

在这种情况下

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

Sometimes you need to have the ORDER BY in each of the sections that need to be combined with 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

回复收藏 0 原文

黎歌 2024-07-14 03:38:14

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

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

回复收藏 0 原文

巴黎夜雨 2024-07-14 03:38:14

我认为这很好地解释了这一点。

以下是使用 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;

由于两个“select”语句之间的列名不同，因此按 ORDER BY 子句中的列在结果集中的位置引用它们会更有利。

在此示例中，我们按 supplier_name / company_name 按升序对结果进行排序，如“ORDER BY 2”所示。

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

取自此处：http://www.techonthenet.com/sql/union.php

I think this does a good job of explaining.

The following is a UNION query that uses an ORDER BY clause:

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.

In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".

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

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

回复收藏 0 原文

不寐倦长更 2024-07-14 03:38:14

用一个具体的例子：

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: （first select的结果，即文件夹优先）

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

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:

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

回复收藏 0 原文

作死小能手 2024-07-14 03:38:14

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

在完成所有联合之后，ORDER BY 子句只需成为最后一个语句即可。您可以将多个集合合并在一起，然后在最后一个集合之后放置 ORDER BY 子句。

Here's an example from 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.

回复收藏 0 原文

向地狱狂奔 2024-07-14 03:38:14

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

工作？记住思考集。使用并集获取所需的集合，然后对其执行操作。

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

回复收藏 0 原文

不及他 2024-07-14 03:38:14

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

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

回复收藏 0 原文

鸠书 2024-07-14 03:38:14

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

（使用别名）

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

(use ALIAS)

回复收藏 0 原文

橘和柠 2024-07-14 03:38:14

这是我见过的最愚蠢的事情，但它确实有效，而且你无法与结果争论。

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 上尝试过这个方法，这三个都有效。

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.

回复收藏 0 原文

素食主义者 2024-07-14 03:38:14

是这样完成的

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

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

回复收藏 0 原文

偷得浮生 2024-07-14 03:38:14

浏览此评论部分时，我发现了两种不同的模式来回答这个问题。遗憾的是，对于 SQL 2012，第二种模式不起作用，因此这是我的“解决方法”：

公共列上的 Order By

这是您可能遇到的最简单的情况。正如许多用户指出的那样，您真正需要做的就是在查询末尾添加一个 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

在不同列上添加 Order By

这实际上是棘手的地方。使用 SQL 2012，我尝试了置顶帖子，但它不起作用。

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 中建立的 Order By 语句使其无法使用。

我能想到的唯一对我有用的解决方案是不使用联合，而是使查询单独运行，然后处理它们。所以基本上，当您想要Order By时，不要使用Union

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"

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

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

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.

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.

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

回复收藏 0 原文

_畞蕅 2024-07-14 03:38:14

通过单独使用顺序，每个子集都会获得顺序，但不是整个集合，这就是您想要合并两个表的顺序。

您应该使用类似这样的方法来获得一个有序集：

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

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

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

回复收藏 0 原文

坦然微笑 2024-07-14 03:38:14

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

所以...

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

不抛出异常

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

So...

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

Does not throw an exception

回复收藏 0 原文

静谧 2024-07-14 03:38:14

如果需要保持内部排序：

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

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

回复收藏 0 原文

岛歌少女 2024-07-14 03:38:14

(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

尝试这个。这对我有用。

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

回复收藏 0 原文

相权↑美人 2024-07-14 03:38:14

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

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

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

回复收藏 0 原文

~没有更多了~