许多应用程序都有网格，一次显示一页数据库表中的数据。他们中的许多人还允许用户选择每页的记录数，按任何列排序以及在结果之间来回导航。

在不将整个表提交给客户端然后过滤客户端上的数据的情况下，实现此模式的好算法是什么？我们如何仅将要显示的记录带给用户？

LINQ是否简化了解决方案？

解决方案

回答

在MS SQL Server 2005及更高版本上，ROW_NUMBER()似乎可以工作：

T-SQL：使用ROW_NUMBER()分页

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具有"跳过"和"获取"方法，可以组合使用这些方法来选择要提取的记录。

看看那些。

对于数据库：SQL Server 2005中的分页

回答

Oracle解决方案：

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，或者尝试复制它的功能。我有一个应用程序，使用LINQ Take和Skip方法检索分页数据。代码看起来像这样：

MyDataContext db = new MyDataContext();
var results = db.Products
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize);

运行SQL Server Profiler显示LINQ正在将该查询转换为SQL，类似于：

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)进行排序，该行号与所需顺序相同(在本例中为"名称")。

回答

我将MS SQL 2005与一些解决方案配合使用。

其中之一是ROW_NUMBER()。但是，就我个人而言，我不喜欢ROW_NUMBER()，因为它不适用于大型结果(我处理的数据库确实很大-超过1TB的数据在第二个运行数千个查询的站点-我们知道-大型社交网站)。

这是我最喜欢的解决方案。

我将使用T-SQL的一种伪代码。

让我们找到按姓氏，姓氏排序的用户的第二页，其中每页有10条记录。

@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

回答

LINQ与.Net 3.5中的lambda表达式和匿名类相结合，极大地简化了这种事情。

查询数据库：

var customers = from c in db.customers
                join p in db.purchases on c.CustomerID equals p.CustomerID
                where p.purchases > 5
                select c;

每页记录数：

customers = customers.Skip(pageNum * pageSize).Take(pageSize);

按任何列排序：

customers = customers.OrderBy(c => c.LastName);

仅从服务器获取选定的字段：

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
                };

这将创建一个静态类型的匿名类，我们可以在其中访问其属性：

var firstCustomer = customer.First();
int id = firstCustomer.CustomerID;

默认情况下，查询结果是延迟加载的，因此，在我们真正需要数据之前，我们无需与数据库进行对话。 .Net中的LINQ还通过保留所做的任何更改的数据上下文，并且仅更新更改的字段，极大地简化了更新。

回答

这里有一个讨论

该技术在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.

恐怕我无法判断它是否比当前接受的答案更好。