在 SQL Server 中组合 ORDER BY 和 UNION
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5551064/
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
Combining ORDER BY AND UNION in SQL Server
提问by Faizal Balsania
How can I get first record of a table and last record of a table in one result-set?
如何在一个结果集中获取表的第一条记录和表的最后一条记录？
This Query fails
此查询失败
SELECT TOP 1 Id,Name FROM Locations ORDER BY Id
UNION ALL
SELECT TOP 1 Id,Name FROM Locations ORDER BY Id DESC
Any help?
有什么帮助吗？
回答by Keith
Put your order byand topstatements into sub-queries:
将您的order byandtop语句放入子查询中：
select first.Id, first.Name
from (
select top 1 *
from Locations
order by Id) first
union all
select last.Id, last.Name
from (
select top 1 *
from Locations
order by Id desc) last
回答by RichardTheKiwi
select * from (
SELECT TOP 1 Id,Name FROM Locations ORDER BY Id) X
UNION ALL
SELECT TOP 1 Id,Name FROM Locations ORDER BY Id DESC
回答by Damien_The_Unbeliever
If you're working on SQL Server 2005 or later:
如果您使用的是 SQL Server 2005 或更高版本：
; WITH NumberedRows as (
SELECT Id,Name,
ROW_NUMBER() OVER (ORDER BY Id) as rnAsc,
ROW_NUMBER() OVER (ORDER BY Id desc) as rnDesc
FROM
Locations
)
select * from NumberedRows where rnAsc = 1 or rnDesc = 1
The only place this won't be like your original query is if there's only one row in the table (in which case my answer returns one row, whereas yours would return the same row twice)
唯一与原始查询不同的地方是表中只有一行（在这种情况下，我的答案返回一行，而您的答案将返回同一行两次）
回答by Siddappa Walake
SELECT TOP 1 Id as sameColumn,Name FROM Locations
UNION ALL
SELECT TOP 1 Id as sameColumn,Name FROM Locations ORDER BY sameColumn DESC
