如何从SQL中返回一页结果？

28 浏览
0 Comments

如何从SQL中返回一页结果？

许多应用程序都有一个以每页显示数据库表中的数据的网格。其中许多应用程序还允许用户选择每页记录的数量，按任意列进行排序，并在结果之间进行导航。

如何在不将整个表格带到客户端并在客户端上过滤数据的情况下实现这种模式的良好算法？如何只将要显示给用户的记录带到客户端？

LINQ是否简化了解决方案？

0

3 答案

0 Comments

如何从 SQL 中返回一页结果？

在使用 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], 等等...
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)，行号与你想要的顺序相同（在这个例子中，按照 Name 排序）。

你知道为什么 LINQ 会将实际的 SELECT 语句嵌套两层吗？这是某个 SQL Server 版本的微小性能优化吗？我觉得第二层可以与第一层合并。

0
0 Comments

如何从SQL返回一页结果？

在数据库中有两种基本的分页方法（假设您使用的是SQL Server）：

使用OFFSET

其他人已经解释了如何使用ROW_NUMBER() OVER()排名函数进行分页。值得一提的是，SQL Server 2012终于包含了对SQL标准OFFSET .. FETCH子句的支持：

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中有一种完全不同但更快的分页方法，这通常被称为“seek方法”，如这篇博客文章中所描述的。

SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score <)
   OR (score =  AND player_id < )
ORDER BY score DESC, player_id DESC

和的值是上一页的最后一条记录的相应值。这样可以获取“下一页”的数据。如果ORDER BY的方向是ASC，则使用>代替<。

使用上述方法，您不能立即跳转到第4页，而必须先获取前面的40条记录。但通常情况下，您也不会跳转那么远。相反，您将获得一个更快的查询，根据索引的不同，可能能够以恒定的时间获取数据。此外，您的页面将保持“稳定”，即使底层数据发生变化（例如在第1页时，您仍在第4页）。

这是在Web应用程序中实现延迟加载更多数据时实现分页的最佳方法。

请注意，“seek方法”也被称为keyset paging。

0
0 Comments

如何从SQL返回一个页面的结果？

在MS SQL Server 2005及以上版本中，ROW_NUMBER()函数似乎能够工作。T-SQL: Paging with ROW_NUMBER()提供了相关的示例代码。

DECLARE @PageNumber AS INT;

DECLARE @PageSize AS INT;

SET @PageNumber = 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 (@PageNumber - 1) * @PageSize + 1

AND @PageNumber * @PageSize

ORDER BY OrderDate

,OrderID;

ROW_NUMBER()函数的分页模拟方法或SQL Server 2012的OFFSET .. FETCH子句对于高页数可能会非常慢。在这种情况下，使用"seek method"可能是一个更好的选择，因为它可以在恒定的时间内进行分页操作。你可以在4guysfromrolla.com/webtech/042606-1.shtml上找到更多关于此方法的信息。

0