如何从SQL返回一页结果?

在MS SQL Server 2005及更高版本上, ROW_NUMBER() 似乎起作用:

T-SQL: Paging with 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,要么尝试复制它的功能。我有一个应用程序,我使用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)以仅返回页面上所需的行号。
三。按行编号对(2)进行排序,行编号与所需的顺序相同(在本例中,按名称)。

在数据库中进行分页的基本方法有两种(我假设您使用的是SQL Server):

使用偏移量

其他人解释了 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方法”。 this blog post here .

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应用程序中延迟加载更多数据时,这是实现分页的最佳方法。

注意,“seek方法”也被称为 keyset paging .

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还通过保留所做任何更改的DataContext,并且只更新所更改的字段,极大地简化了更新。

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

我在MS SQL 2005中使用了一些解决方案。

其中一个是row_number()。但就我个人而言,我不喜欢row_number(),因为它不适用于大的结果(我所处理的数据库非常大——超过1TB的数据在第二秒内运行数千个查询——你知道——大的社交网站)。

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

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

让我们找到按名字、姓氏排序的第2页用户,其中每一页有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有Skip和Take方法,可以组合这些方法来选择要提取哪些记录。

看看这些。

对于DB: Pagination In SQL Server 2005

关于这个有个讨论 Here

这项技术在78ms内从150000行数据库中获取100000页。

使用优化器知识和设置行数,页面中请求的第一个EmployeeID存储在起始点的局部变量中。接下来,将rowcount设置为@maximumrows中请求的最大记录数。这允许以更高效的方式对结果集进行分页。使用此方法还可以利用表上预先存在的索引,因为它直接指向基表,而不是本地创建的表。

恐怕我无法判断它是否比目前公认的答案更好。