如何从SQL返回结果页面？ [英] How to return a page of results from SQL?

问题描述

许多应用程序都有一个从数据库表中的一个页面同时显示的数据网格。他们中许多人也让用户选择每页记录的数量，排序任何列，导航来回通过的结果。

Many applications have grids that display data from a database table one page at a time. Many of them also let the user pick the number of records per page, sort by any column, and navigate back and forth through the results.

什么是好的算法来实现这个模式不使整个表到客户端，然后过滤客户机上的数据。你怎么只带要显示给用户的记录？

What's a good algorithm to implement this pattern without bringing the entire table to the client and then filtering the data on the client. How do you bring just the records you want to display to the user?

是否LINQ简化的解决方案？

Does LINQ simplify the solution?

推荐答案

在MS SQL Server 2005及以上的 ROW_NUMBER（）似乎工作：

On MS SQL Server 2005 and above, ROW_NUMBER() seems to work:

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;

这篇关于如何从SQL返回结果页面？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！