SQL 使用一个 select 语句返回特定范围之间的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13422613/
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
Return rows between a specific range, with one select statement
提问by user1823901
I'm looking to some expresion like this (using SQL Server 2008)
我正在寻找这样的表达（使用 SQL Server 2008）
SELECT TOP 10 columName FROM tableName
But instead of that I need the values between 10 and 20. And I wonder if there is a way of doing it using only one SELECT statement.
但不是我需要 10 到 20 之间的值。我想知道是否有一种方法可以只使用一个 SELECT 语句来做到这一点。
For example this is useless:
例如这是没用的：
SELECT columName FROM
(SELECT ROW_NUMBER() OVER(ORDER BY someId) AS RowNum, * FROM tableName) AS alias
WHERE RowNum BETWEEN 10 AND 20
Because the select inside brackets is already returning all the results, and I'm looking to avoid that, due to performance.
因为括号内的 select 已经返回所有结果，由于性能原因，我希望避免这种情况。
回答by Gordon Linoff
There is a trick with row_numberthat does not involve sorting all the rows.
有一个技巧row_number不涉及对所有行进行排序。
Try this:
尝试这个：
SELECT columName
FROM (SELECT ROW_NUMBER() OVER(ORDER BY (select NULL as noorder)) AS RowNum, *
FROM tableName
) as alias
WHERE RowNum BETWEEN 10 AND 20
You cannot use a constant in the order by. However, you can use an expression that evaluates to a constant. SQL Server recognizes this and just returns the rows as encountered, properly enumerated.
您不能在order by. 但是，您可以使用计算结果为常量的表达式。SQL Server 识别出这一点，并只返回遇到的行，正确枚举。
回答by RAS
Use SQL Server 2012 to fetch/skip!
使用 SQL Server 2012 获取/跳过！
SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, UnitPrice, LineTotal
FROM AdventureWorks2012.Sales.SalesOrderDetail
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
There's nothing better than you're describing for older versions of sql server. Maybe use CTE, but unlikely to make a difference.
没有什么比您为旧版本的 sql server 描述的更好的了。也许使用 CTE，但不太可能有所作为。
WITH NumberedMyTable AS
(
SELECT
Id,
Value,
ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
FROM
MyTable
)
SELECT
Id,
Value
FROM
NumberedMyTable
WHERE
RowNumber BETWEEN @From AND @To
or, you can remove top 10 rows and then get next 10 rows, but I double anyone would want to do that.
或者，您可以删除前 10 行，然后获取下 10 行，但我将任何人都想这样做。
回答by Laurence
Why do you think SQL Server would evaluate the entire inner query? Assuming your sort column is indexed, it'll just read the first 20 values. If you're really nervous you could do this:
为什么您认为 SQL Server 会评估整个内部查询？假设您的排序列已编入索引，它只会读取前 20 个值。如果你真的很紧张，你可以这样做：
Select
Id
From (
Select Top 20 -- note top 20
Row_Number() Over(Order By Id) As RowNum,
Id
From
dbo.Test
Order By
Id
) As alias
Where
RowNum Between 10 And 20
Order By
Id
but I'm pretty sure the query plan is the same either way.
但我很确定查询计划是相同的。
(Really) Fixed as per Aaron's comment.
（真的）根据 Aaron 的评论修复。
回答by Aleksandr Fedorenko
One more option
多一个选择
SELECT TOP(11) columName
FROM dbo.tableName
ORDER BY
CASE WHEN ROW_NUMBER() OVER (ORDER BY someId) BETWEEN 10 AND 20
THEN ROW_NUMBER() OVER (ORDER BY someId) ELSE NULL END DESC
回答by user3688672
You could create a temp table that is ordered the way you want like:
您可以创建一个按您想要的方式排序的临时表，例如：
SELECT ROW_NUMBER() OVER(ORDER BY someId) AS RowNum, * FROM tableName into ##tempTable ...
SELECT ROW_NUMBER() OVER(ORDER BY someId) AS RowNum, * FROM tableName into ##tempTable ...
That way you have an ordered list of rows. and can just query by row number the subsequent times instead of doing the inner query multiple times.
这样你就有了一个有序的行列表。并且可以只按行号查询后续次数，而不是多次执行内部查询。
