我如何编写 LINQ 的。跳过(1000)。采取(100)在纯 SQL？

什么是 LINQ 中 .Skip()方法的 SQL 等价物？

例如: 我想从一个特定的数据库表中选择行1000-1100。

只使用 SQL 可以做到这一点吗？还是需要选择整个表，然后在内存中查找行？如果可能的话，我最好避免这种情况，因为桌子可能会很大。

56008 次浏览
小开

No, but you could emulate MySQL's LIMIT clause (Stack Overflow link) to achieve the same result.

小开

In SQL Server 2005 and above you can use ROW_NUMBER function. eg.

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
小开

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.

It won't be as elegant but it gets the job done.

小开

LINQ to SQL does this by using a ROW_NUMBER windowing function:

  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 may result 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...

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).

小开
最佳答案

SQL Server 2012 and above have added this syntax:

SELECT *
FROM Sales.SalesOrderHeader
ORDER BY OrderDate
OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY
小开

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

提交答案