SQL 选择顶行和底行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2927475/
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
Select top and bottom rows
提问by Adrian Faciu
I'm using SQL Server 2005 and I'm trying to achieve something like this: I want to get the first x rows and the last x rows in the same select statement.
我正在使用 SQL Server 2005 并且我正在尝试实现这样的目标：我想在同一个 select 语句中获取前 x 行和最后 x 行。
SELECT TOP(5) BOTTOM(5)
Of course BOTTOMdoes not exist, so I need another solution. I believe there is an easy and elegant solution that I'm not getting. Doing the select again with GROUP BY DESCis not an option.
当然BOTTOM不存在，所以我需要另一个解决方案。我相信有一个我没有得到的简单而优雅的解决方案。再次选择 withGROUP BY DESC不是一种选择。
回答by Ralf de Kleine
Using a union is the only thing I can think of to accomplish this
使用联合是我能想到的唯一方法来实现这一点
select * from (select top(5) * from logins order by USERNAME ASC) a
union
select * from (select top(5) * from logins order by USERNAME DESC) b
回答by hgulyan
Check the link
检查链接
SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL
SQL SERVER – 如何使用 T-SQL 一起检索顶部和底部行
Did you try to using rownumber?
您是否尝试使用rownumber？
SELECT *
FROM
(SELECT *, ROW_NUMBER() OVER (Order BY columnName) as TopFive
,ROW_NUMBER() OVER (Order BY columnName Desc) as BottomFive
FROM Table
)
WHERE TopFive <=5 or BottomFive <=5
http://www.sqlservercurry.com/2009/02/select-top-n-and-bottom-n-rows-using.html
http://www.sqlservercurry.com/2009/02/select-top-n-and-bottom-n-rows-using.html
回答by Paul
I think you've two main options:
我认为你有两个主要选择：
SELECT TOP 5 ...
FROM ...
ORDER BY ... ASC
UNION
SELECT TOP 5 ...
FROM ...
ORDER BY ... DESC
Or, if you know how many items there are in the table:
或者，如果您知道表中有多少项：
SELECT ...
FROM (
SELECT ..., ROW_NUMBER() OVER (ORDER BY ... ASC) AS intRow
FROM ...
) AS T
WHERE intRow BETWEEN 1 AND 5 OR intRow BETWEEN @Number - 5 AND @Number
回答by Chris W
Is it an option for you to use a union?
您可以选择使用工会吗？
E.g.
例如
select top 5 ... order by {specify columns asc}
union
select top 5 ... order by {specify columns desc}
回答by Sako73
No real difference between this and the union that I'm aware of, but technically it is a single query.
这和我所知道的工会之间没有真正的区别，但从技术上讲，它是一个单一的查询。
select t.*
from table t
where t.id in (select top 5 t2.id from table t2 order by MyColumn)
or
t.id in (select top 5 t2.id from table t2 order by MyColumn desc);
回答by Salil
i guess you have to do it using subquery only
我想你必须只使用子查询
select * from table where id in (
(SELECT id ORDER BY columnName LIMIT 5) OR
(SELECT id ORDER BY columnName DESC LIMIT 5)
)
select * from table where id in (
(SELECT TOP(5) id ORDER BY columnName) OR
(SELECT TOP(5) id ORDER BY columnName DESC)
)
EDITED
已编辑
select * from table where id in (
(SELECT TOP 5 id ORDER BY columnName) OR
(SELECT TOP 5 id ORDER BY columnName DESC)
)
回答by TomTom
Then you are out - doing the select again IS the only option, unless you want to pull in the complete result set and then throwing away everything in between.
然后你就出去了 - 再次选择是唯一的选择，除非你想拉入完整的结果集然后扔掉中间的所有东西。
ANY sql I cna think of is the same way - for the bottom you need to know first either how many items you have (materialize everything or use count(*)) or a reverse sort order.
我想的任何 sql 都是相同的方式 - 对于底部，您首先需要知道您有多少项目（具体化所有内容或使用计数（*））或反向排序。
Sorry if that does not suit you, but at the end.... reality does not care, and I do not see any other way to do that.
对不起，如果这不适合你，但最后......现实并不关心，我看不出有任何其他方式可以做到这一点。
回答by Ernest
I had to do this recently for a very large stored procedure; if your query is quite large, and you want to minimize the amount of queries you could declare a @tempTable, insert into that @tempTable then query from that @tempTable,
我最近不得不为一个非常大的存储过程执行此操作；如果您的查询非常大，并且您想最大限度地减少可以声明@tempTable 的查询数量，请插入该@tempTable，然后从该@tempTable 进行查询，
DECLARE @tempTable TABLE ( columns.. )
INSERT INTO @tempTable
VALUES ( SELECT.. your query here ..)
SELECT TOP(5) columns FROM @tempTable ORDER BY column ASC -- returns first to last
SELECT TOP(5) columns FROM @tempTable ORDER BY column DESC -- returns last to first
回答by Theodore Lee
SELECT *
FROM (
SELECT x, rank() over (order by x asc) as rown
FROM table
) temp
where temp.rown = 1
or temp.rown = (select count(x) from table)
