首页 / 知识

关于.net：如何从SQL返回结果页面？

2023-04-12 03:47:00

How to return a page of results from SQL?

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

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

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.

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

返回结果数据用户客户端