SQL 如何在sql中使用order by和union all？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15470191/
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 use order by with union all in sql?
提问by Wella
I tried the sql query given below:
我尝试了下面给出的 sql 查询：
SELECT * FROM (SELECT *
FROM TABLE_A ORDER BY COLUMN_1)DUMMY_TABLE
UNION ALL
SELECT * FROM TABLE_B
It results in the following error:
它导致以下错误：
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了 TOP 或 FOR XML。
I need to use order by in union all. How do I accomplish this?
我需要在 union all 中使用 order by。我该如何实现？
回答by John Woo
SELECT *
FROM
(
SELECT * FROM TABLE_A
UNION ALL
SELECT * FROM TABLE_B
) dum
-- ORDER BY .....
but if you want to have all records from Table_Aon the top of the result list, the you can add user define value which you can use for ordering,
但是如果您想将所有记录Table_A放在结果列表的顶部，您可以添加用户定义的值，您可以将其用于排序，
SELECT *
FROM
(
SELECT *, 1 sortby FROM TABLE_A
UNION ALL
SELECT *, 2 sortby FROM TABLE_B
) dum
ORDER BY sortby
回答by Giorgi Nakeuri
You don't really need to have parenthesis. You can sort directly:
你真的不需要括号。可以直接排序：
SELECT *, 1 AS RN FROM TABLE_A
UNION ALL
SELECT *, 2 AS RN FROM TABLE_B
ORDER BY RN, COLUMN_1
回答by Fandango68
Not an OP direct response, but I thought I would jimmy in here responding to the the OP's ERROR messsage, which may point you in another direction entirely!
不是 OP 的直接响应，但我想我会在这里回应 OP 的 ERROR 消息，这可能会完全指向另一个方向！
All these answers are referring to an overallORDER BY once the record set has been retrieved and you sort the lot.
一旦检索到记录集并对批次进行排序，所有这些答案都指的是整体ORDER BY。
What if you want to ORDER BY each portionof the UNION independantly, and still have them "joined" in the same SELECT?
如果您想独立地对 UNION 的每个部分进行 ORDER BY ，并且仍然让它们在同一个 SELECT 中“加入”怎么办？
SELECT pass1.* FROM
(SELECT TOP 1000 tblA.ID, tblA.CustomerName
FROM TABLE_A AS tblA ORDER BY 2) AS pass1
UNION ALL
SELECT pass2.* FROM
(SELECT TOP 1000 tblB.ID, tblB.CustomerName
FROM TABLE_B AS tblB ORDER BY 2) AS pass2
Note the TOP 1000 is an arbitary number. Use a big enough number to capture all of the data you require.
请注意，TOP 1000 是一个任意数字。使用足够大的数字来捕获您需要的所有数据。
回答by shambhu
Select 'Shambhu' as ShambhuNewsFeed,Note as [News Fedd],NotificationId
from Notification with(nolock) where DesignationId=@Designation
Union All
Select 'Shambhu' as ShambhuNewsFeed,Note as [Notification],NotificationId
from Notification with(nolock)
where DesignationId=@Designation
order by NotificationId desc
