当前位置：首页 > 问答 > 正文

写个存储过程来分页拿数据，sql server里怎么搞比较方便点

芮以莲
问答
2025-12-24 01:07:23
1

在 SQL Server 中，要实现分页获取数据，也就是我们常说的“翻页”，有几种方法，但要说最方便、最现代、也是目前最推荐的方法，就是使用 OFFSET ... FETCH ... 子句，这个功能是从 SQL Server 2012 版本开始引入的，它让分页查询变得非常直观和简单。

核心方法：使用 OFFSET ... FETCH ...

它的基本思路就像是在下指令：“跳过前 N 行，然后取 M 行出来。” 这完全符合我们分页的直觉。

写个存储过程来分页拿数据，sql server里怎么搞比较方便点

一个最基础的分页存储过程大概长这样：

CREATE PROCEDURE GetPagedData
    @PageNumber INT,        -- 要请求第几页
    @PageSize INT           -- 每页显示多少条记录
AS
BEGIN
    -- 设置不返回受影响行数的计数，让结果更干净
    SET NOCOUNT ON;
    -- 计算要跳过的行数，比如第2页，每页10条，就跳过 (2-1)*10 = 10 条
    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
    -- 执行查询
    SELECT *
    FROM YourTableName       -- 你的表名
    ORDER BY SomeColumn      -- 必须有一个排序规则，否则分页顺序会乱
    OFFSET @Offset ROWS      -- 跳过指定行数
    FETCH NEXT @PageSize ROWS ONLY; -- 只获取下一页的数据
END

来详细解释一下这个存储过程的每一部分：

参数 (@PageNumber 和 @PageSize)：这是存储过程与外部程序（比如你的网站后端或应用程序）沟通的桥梁，前端告诉存储过程：“我要看第几页（@PageNumber），并且每页要显示多少条（@PageSize）。”
SET NOCOUNT ON：这是一条优化指令，它告诉 SQL Server 不要在每个 SQL 语句执行后返回一个消息说“有多少行被影响”，对于这种纯查询的存储过程，关掉它可以减少不必要的网络流量，让结果集更干净。
计算偏移量 (@Offset)：这是分页逻辑的核心计算，要显示第 N 页的数据，意味着你需要跳过前面 (N-1) 页的所有数据，所以跳过的行数就是 (当前页码 - 1) * 每页大小。
ORDER BY 子句：这是使用 OFFSET ... FETCH 时绝对必不可少的！ 数据库表本身是无序的集合，你必须通过 ORDER BY 指定一个或多个列来定义一个稳定的排序顺序（比如按创建时间倒序、按ID排序），没有这个顺序，每次分页查询返回的结果行顺序可能都不一样，分页就乱套了。
OFFSET @Offset ROWS：这就是执行“跳过”动作的地方，它告诉数据库：“按照刚才 ORDER BY 排好的顺序，跳过最前面的 @Offset 行。”
FETCH NEXT @PageSize ROWS ONLY：在跳过之后，它紧接着说：“从下一行开始，只给我 @PageSize 条记录。”

举个例子： 假设你的表叫 Products（产品表），你想按 ProductID 排序，每页显示 10 条产品。

写个存储过程来分页拿数据，sql server里怎么搞比较方便点

当调用 EXEC GetPagedData @PageNumber=1, @PageSize=10 时，@Offset = (1-1)*10 = 0，查询会跳过 0 行，返回第 1 到第 10 条记录。
当调用 EXEC GetPagedData @PageNumber=3, @PageSize=10 时，@Offset = (3-1)*10 = 20，查询会跳过前 20 条记录，返回第 21 到第 30 条记录。

如何获取总记录数？

在实际应用中,分页控件通常不仅需要当前页的数据，还需要知道总共有多少条记录，这样才能计算出总页数，我们可以在同一个存储过程里返回这个信息，这里会用到 SQL Server 的 OUTPUT 参数。

改进后的存储过程如下：

写个存储过程来分页拿数据，sql server里怎么搞比较方便点

CREATE PROCEDURE GetPagedDataWithTotal
    @PageNumber INT,
    @PageSize INT,
    @TotalCount INT OUTPUT    -- 新增一个输出参数，用于返回总记录数
AS
BEGIN
    SET NOCOUNT ON;
    -- 计算总记录数
    SELECT @TotalCount = COUNT(*)
    FROM YourTableName;
    -- 计算偏移量并获取分页数据
    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
    SELECT *
    FROM YourTableName
    ORDER BY SomeColumn
    OFFSET @Offset ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END

在调用这个存储过程时,你需要用一个变量来接收 @TotalCount 输出参数，在另一个 SQL 脚本中这样调用：

-- 声明一个变量来接收总记录数
DECLARE @TotalRecs INT;
-- 执行存储过程，传入参数，并用变量接收输出参数
EXEC GetPagedDataWithTotal
    @PageNumber = 2,
    @PageSize = 10,
    @TotalCount = @TotalRecs OUTPUT; -- 注意这里的 OUTPUT 关键字
-- 你可以看到分页数据的结果集，@TotalRecs 变量里存储了总记录数
SELECT @TotalRecs AS '总记录数';

性能上的考虑和一些小提示

索引是关键：确保 ORDER BY 后面用到的列上有合适的索引，如果按 CreateTime DESC 排序，那么在 CreateTime 上建一个索引会极大地提高分页查询的速度，尤其是在数据量很大的时候，没有索引，数据库每次都要对整个表进行排序，非常慢。
OFFSET 的弱点：OFFSET 在处理非常靠后的页码时（比如第 10000 页），性能会下降，因为它需要先扫描并跳过前面的大量行，对于超深分页，有更优化的方法（比如使用“游标”或“seek method”），但那些更复杂，对于大多数常规应用，OFFSET 是完全足够且最方便的。
过滤数据：如果你的分页需要带查询条件，比如只查询某个类别的产品，只需在 WHERE 子句中加上条件，并且在计算总记录数 @TotalCount 的语句中也加上同样的条件即可。