C# 检索 SQL Server 表中特定范围的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1023841/
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
retrieve specific range of rows in a SQL Server table
提问by George2
I have a table structure like (OrderID [uniqueidentifier], OrderDesciption [nvarchar]), I am using ADO.Net + C# + VSTS 2008 + SQL Server 2008. The table is big, and I want to let client give me two inputs, begin range index and end range index, and I will return specific rows of the table which is in the range (between begin range index and end range index).
我有一个像 (OrderID [uniqueidentifier], OrderDescription [nvarchar]) 这样的表结构，我使用的是 ADO.Net + C# + VSTS 2008 + SQL Server 2008。表很大，我想让客户端给我两个输入，开始范围索引和结束范围索引，我将返回表中范围内（开始范围索引和结束范围索引之间）的特定行。
For example, if the client inputs to me 50, 100, and I want to return the 50th row until the 100th row.
比如客户端给我输入50、100，我想返回第50行直到第100行。
thanks in advance, George
提前致谢，乔治
回答by marc_s
Basically, your best bet in SQL Server 2005 and 2008 is a CTE - Common Table Expression - with a ROW_NUMBER() function in it - something like this:
基本上，您在 SQL Server 2005 和 2008 中的最佳选择是 CTE - 公共表表达式 - 带有 ROW_NUMBER() 函数 - 如下所示：
WITH MyOrders AS
(
SELECT
OrderID,
OrderDescription,
ROW_NUMBER() OVER (ORDER BY OrderID) as 'RowNum'
FROM YourOrders
)
SELECT * FROM MyOrders
WHERE RowNum BETWEEN 50 AND 100
But this requires a useful and suitable ORDER BYclause, and ordering by a GUID is really not a good idea. DATETIME or an ever-increasing ID would be best.
但这需要一个有用且合适的ORDER BY子句，并且按 GUID 排序确实不是一个好主意。DATETIME 或不断增加的 ID 会是最好的。
Marc
马克
回答by balint
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderId) as row FROM Orders) a WHERE row > 5 and row <= 10
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderId) as row FROM Orders) a WHERE row > 5 and row <= 10
回答by Marc Gravell
You can use ROW_NUMBERin TSQL (2005 onwards) to do this:
您可以ROW_NUMBER在 TSQL（2005 年以后）中使用：
SELECT ID, Foo, Bar
FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS Row,
ID, Foo, Bar
FROM SomeTable) tmp
WHERE Row >= 50 AND Row <= 100
Or with LINQ-to-SQL etc:
或者使用 LINQ-to-SQL 等：
var qry = ctx.Table.Skip(50).Take(50); // or similar
回答by kiewic
Try this, the result will be ordered by OrderIDcolumn. Change MyTablewith your table.
试试这个，结果将按OrderID列排序。用你的桌子改变MyTable。
SELECT *
FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY OrderID) AS row FROM MyTable) a
WHERE row > 50 AND row <= 100
The selected rows are from 50to 100, but notice that the 50th row is not included in the result.
选定的行是从50到100，但请注意，结果中不包含第 50 行。
