许多应用程序都有网格，一次显示一页数据库表中的数据。 他们中的许多人还允许用户选择每页的记录数，按任何列排序以及在结果之间来回导航。
在不将整个表带到客户端然后过滤客户端数据的情况下，实现此模式的好算法是什么？ 您如何只将要显示的记录带给用户？
LINQ是否简化了解决方案？
在MS SQL Server 2005及更高版本上，ROW_NUMBER()似乎可以工作：
T-SQL：使用ROW_NUMBER()分页
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
| DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;
WITH OrdersRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
,OrderID
,OrderDate
,CustomerID
,EmployeeID
FROM dbo.Orders
)
SELECT *
FROM OrdersRN
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize
ORDER BY OrderDate
,OrderID; |
我建议您使用LINQ，或尝试复制它的功能。我有一个应用程序，使用LINQ Take和Skip方法检索分页数据。代码看起来像这样：
1
2
3
4
| MyDataContext db = NEW MyDataContext();
var results = db.Products
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize); |
运行SQL Server Profiler显示LINQ正在将该查询转换为SQL，类似于：
1
2
3
4
5
6
7
8
9
10
11
| SELECT [ProductId], [Name], [Cost], AND so ON...
FROM (
SELECT [ProductId], [Name], [Cost], [ROW_NUMBER]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS [ROW_NUMBER],
[ProductId], [Name], [Cost]
FROM [Products]
)
WHERE [ROW_NUMBER] BETWEEN 10 AND 20
)
ORDER BY [ROW_NUMBER] |
用简单的英语：
1.过滤行，然后使用ROW_NUMBER函数按所需顺序添加行号。
2.筛选(1)仅返回您要在页面上显示的行号。
3.按行号对(2)进行排序，该行号与所需顺序相同(在本例中为"名称")。
在数据库中进行分页基本上有两种方法(我假设您正在使用SQL Server)：
使用偏移
其他人已经解释了ROW_NUMBER() OVER()排序功能如何用于执行页面。值得一提的是，SQL Server 2012最终包括了对SQL标准OFFSET .. FETCH子句的支持：
1
2
3
4
| SELECT first_name, last_name, score
FROM players
ORDER BY score DESC
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY |
如果您使用的是SQL Server 2012，则向后兼容不是问题，您可能应该首选此子句，因为在极端情况下，SQL Server会更优化地执行该子句。
使用SEEK方法
在SQL中执行分页的方法完全不同，速度更快，但是鲜为人知。如本文博客文章所述，这通常称为"搜索方法"。
1
2
3
4
5
| SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC |
@previousScore和@previousPlayerId值是上一页中最后一条记录的相应值。这使您可以获取"下一页"页面。如果ORDER BY方向为ASC，则只需使用>。
使用上述方法，您必须先获取前40条记录，才能立即跳至第4页。但是通常，您还是不想跳得那么远。相反，您将获得一个更快的查询，该查询可能能够在恒定时间内获取数据，具体取决于您的索引编制。另外，无论基础数据是否发生变化(例如，在第1页上，而在第4页上)，您的页面仍保持"稳定"状态。
例如，这是在Web应用程序中延迟加载更多数据时实现分页的最佳方法。
注意，"搜索方法"也称为键集分页。
LINQ与.Net 3.5中的lambda表达式和匿名类相结合，极大地简化了这种事情。
查询数据库：
1
2
3
4
| var customers = FROM c IN db.customers
JOIN p IN db.purchases ON c.CustomerID equals p.CustomerID
WHERE p.purchases > 5
SELECT c; |
每页记录数：
1
| customers = customers.Skip(pageNum * pageSize).Take(pageSize); |
按任何列排序：
1
| customers = customers.OrderBy(c => c.LastName); |
仅从服务器获取所选字段：
1
2
3
4
5
6
7
8
9
| var customers = FROM c IN db.customers
JOIN p IN db.purchases ON c.CustomerID equals p.CustomerID
WHERE p.purchases > 5
SELECT NEW
{
CustomerID = c.CustomerID,
FirstName = c.FirstName,
LastName = c.LastName
}; |
这将创建一个静态类型的匿名类，您可以在其中访问其属性：
1
2
| var firstCustomer = customer.First();
INT id = firstCustomer.CustomerID; |
默认情况下，查询结果是延迟加载的，因此，在您真正需要数据之前，您无需与数据库进行对话。 .Net中的LINQ还通过保留所做的任何更改的数据上下文，并且仅更新更改的字段，极大地简化了更新。
我使用MS SQL 2005时有一些解决方案。
其中之一是ROW_NUMBER()。但是，就我个人而言，我不喜欢ROW_NUMBER()，因为它不适用于大型结果(我正在处理的数据库非常大-超过1TB的数据每秒可运行数千个查询-您知道-大型社交网络现场)。
这是我最喜欢的解决方案。
我将使用T-SQL的一种伪代码。
让我们找到按姓氏，姓氏排序的用户第二页，其中每页有10条记录。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| @page = 2 -- input parameter
@SIZE = 10 -- can be optional input parameter
IF @page < 1 THEN BEGIN
@page = 1 -- check page number
END
@START = (@page-1) * @SIZE + 1 -- @page starts at record no @start
-- find the beginning of page @page
SELECT TOP (@START)
@forename = forename,
@surname = surname
@id = id
FROM
users
ORDER BY
forename,
surname,
id -- to keep correct order in case of have two John Smith.
-- select @size records starting from @start
SELECT TOP (@SIZE)
id,
forename,
surname
FROM
users
WHERE
(forename = @forename AND surname = @surname AND id >= @id) -- the same name and surname, but bigger id
OR (forename = @forename AND surname > @surname) -- the same name, but bigger surname, id doesn't matter
OR (forename > @forename) -- bigger forename, the rest doesn't matter
ORDER BY
forename,
surname,
id |
Oracle解决方案：
1
2
3
4
5
6
| SELECT * FROM (
SELECT a.*, rownum rnum FROM (
YOUR_QUERY_GOES_HERE -- including the order by
) a
WHERE rownum <= MAX_ROW
) WHERE rnum >= MIN_ROW |
实际上，LINQ具有"跳过"和"获取"方法，可以将它们组合起来以选择要提取的记录。
看看那些。
对于数据库：SQL Server 2005中的分页
这里有一个讨论
该技术在78毫秒内从150,000行数据库中获取页码100,000
Using optimizer knowledge and SET ROWCOUNT, the first EmployeeID in the page that is requested is stored in a local variable for a starting point. Next, SET ROWCOUNT to the maximum number of records that is requested in @maximumRows. This allows paging the result set in a much more efficient manner. Using this method also takes advantage of pre-existing indexes on the table as it goes directly to the base table and not to a locally created table.
恐怕我无法判断它是否比当前接受的答案更好。
