如何在纯 SQL 中编写 LINQ 的 .Skip(1000).Take(100)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1744802/
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 do I write LINQ's .Skip(1000).Take(100) in pure SQL?
提问by Ray
What is the SQL equivalent of the .Skip()method in LINQ?
.Skip()LINQ中该方法的 SQL 等价物是什么？
For example: I would like to select rows 1000-1100 from a specific database table.
例如：我想从特定的数据库表中选择第 1000-1100 行。
Is this possible with just SQL? Or do I need to select the entire table, then find the rows in memory? I'd ideally like to avoid this, if possible, since the table can be quite large.
仅使用 SQL 就可以做到这一点吗？还是我需要选择整个表，然后在内存中查找行？如果可能的话，我最好避免这种情况，因为桌子可能非常大。
采纳答案by Dan Diplo
In SQL Server 2005 and above you can use ROW_NUMBERfunction. eg.
在 SQL Server 2005 及更高版本中，您可以使用ROW_NUMBER函数。例如。
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 51 AND 60; --BETWEEN is inclusive
回答by John Gietzen
SQL Server 2012 and above have added this syntax:
SQL Server 2012 及更高版本添加了以下语法：
SELECT *
FROM Sales.SalesOrderHeader
ORDER BY OrderDate
OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY
回答by Remus Rusanu
LINQ to SQL does this by using a ROW_NUMBER windowing function:
LINQ to SQL 通过使用 ROW_NUMBER 窗口函数来做到这一点：
SELECT a,b,c FROM
(SELECT a,b,c, ROW_NUMBER() OVER (ORDER BY ...) as row_number
FROM Table) t0
WHERE to.row_number BETWEEN 1000 and 1100;
This works, but the need to manufacture the row_number from the ORDER BY mayresult in your query being sorted on the server side and cause performance problems. Even when an index can satisfy the ORDER BY requirement, the query still has to count 1000 rows before startting to return results. All too often developers forget this and just throw a pagination control over a 5 mil rows table and wonder why the first page is returned so much faster than the last one...
这有效，但需要从 ORDER BY 制造 row_number可能会导致您的查询在服务器端排序并导致性能问题。即使索引可以满足 ORDER BY 要求，查询仍然需要计数 1000 行才能开始返回结果。开发人员经常忘记这一点，只是对一个 500 万行的表进行分页控制，并想知道为什么第一页的返回速度比最后一页快得多......
None the less, using ROW_NUMBER() is probably the best balance between ease of use and good performance, provided you make sure you avoid the sort (the ORDER BY condition can be satisified by an index).
尽管如此，使用 ROW_NUMBER() 可能是易用性和良好性能之间的最佳平衡，前提是您确保避免排序（ORDER BY 条件可以通过索引满足）。
回答by Fereydoon Barikzehy
Try this one:
试试这个：
select * from [Table-Name] order by [Column-Name]
offset [Skip-Count] rows
FETCH NEXT [Take-Count] rows only
Example:
例子：
select * from Personals order by Id
offset 10 rows --------->Skip 10
FETCH NEXT 15 rows only --------->Take 15
回答by Joseph
Do this:
做这个：
Run .Skip(1000).Take(100) on a LINQ to SQL datacontext and look at the SQL output. It will generate a SQL statement for you that does what you're describing.
在 LINQ to SQL 数据上下文上运行 .Skip(1000).Take(100) 并查看 SQL 输出。它将为您生成一个 SQL 语句，执行您所描述的操作。
It won't be as elegant but it gets the job done.
它不会那么优雅，但可以完成工作。
回答by Mike Atlas
No, but you could emulate MySQL's LIMIT clause(Stack Overflow link) to achieve the same result.
不，但您可以模拟 MySQL 的 LIMIT 子句（堆栈溢出链接）来实现相同的结果。
