SQL Server 存储过程分页：取消游标，使用临时表与联合查询实现精准结果

阿橙

发布时间：2026-10-16| 3人浏览过

原创

在数据库开发中，处理主从表（1:N）关联的分页查询常遇到数据条数异常的问题。直接对关联结果进行 ROW_NUMBER 排序分页，会使返回的记录数基于明细行而非主表，导致前端展示数量不符。本文将分析这一现象的成因，并提供一种高效、准确的替代方案：利用临时表分离分页逻辑与明细关联，从而避免使用性能较差的游标。

问题分析：关联查询为何会导致分页数据不准

在进行主表与子表（如订单与订单明细）的内连接或左连接查询时，一条主表记录可能对应多条子表记录。如果直接在最终的联合结果集上应用 ROW_NUMBER() 函数进行分页（例如取第 10-20 条），实际上是在对“订单+明细”的组合行进行计数。

这意味着：

即使只想要 10 个订单，由于每个订单可能有多个明细行，最终返回的总行数会远大于 10。
显示的“10 条结果”实际上是 10 条明细信息，而非 10 个完整的订单实体。

传统的解决方案有两种：

代码层循环：先在 C# 等语言中查询出主表 ID 列表，再遍历该列表逐条查询数据库获取明细。这种方式会导致大量的数据库往返交互（N+1 查询问题），性能极低。
存储过程游标：在数据库中建立游标，遍历主表临时表的结果，逐条查询子表并插入到子表临时表中。虽然减少了网络开销，但游标会锁定行资源，且执行效率通常低于集合操作，仅建议在主表本身为临时表以减少锁影响时使用。

优化方案：基于临时表的两次查询策略

为了兼顾准确性与性能，推荐采用“先分页，后关联”的策略。核心思路是：第一步仅在主表上执行 ROW_NUMBER 分页，提取符合条件的少量主表 ID 存入临时表；第二步再将临时表与子表进行 JOIN 查询。 这样既保证了返回的是准确数量的主表记录及其全部明细，又避免了游标的缺点。

第1步：构建主表分页临时表

首先，创建一个临时表用于存储分页后的主表关键信息。使用 ROW_NUMBER() OVER (ORDER BY ...) 对主表进行排序，并根据参数范围（BETWEEN）截取目标页面数据。注意 ROW_NUMBER() 返回的数据类型为 BIGINT。

-- 创建临时表存放分页后的主表索引
CREATE TABLE #order (
    number BIGINT,
    orderseqno VARCHAR(36)
);

-- 将满足条件并按日期倒序排列的第 10 到 20 条订单编号插入临时表
INSERT INTO #order
SELECT * FROM (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY oi.CreateDate DESC) AS rownumber,
        oi.OrderSeqNO
    FROM OrderInfo oi 
    WHERE oi.OrderSeqNO LIKE '%2%'
) AS o
WHERE o.rownumber BETWEEN 10 AND 20;

第2步：关联查询明细并释放资源

利用已生成的临时表 #order 作为驱动表，与子表 OrderDetail 进行内连接（INNER JOIN）。由于临时表中只有极少量的主键记录，这次连接操作的开销非常小，能快速返回所有需要的详细信息。最后记得删除临时表以释放内存。

-- 通过临时表关联子表，获取完整明细数据
SELECT * FROM #order 
INNER JOIN OrderDetail od ON od.OrderSeqNO = #order.orderseqno;

-- 清理临时表
DROP TABLE #order;

完整存储过程示例（含备用游标方案）

以下是一个完整的存储过程结构示例，展示了如何建立主表和子表的临时表，并使用上述推荐的分页方式。同时附带了游标实现的代码供对比参考，但在生产环境中建议优先使用临时表方案。

-- =============================================
-- 使用 ROW_NUMBER + 临时表的高效分页存储过程
-- =============================================

-- 1. 建立主表临时表，包含主表需要展示的所有字段
CREATE TABLE #temp (
    rownumber bigint,
    orderseqno VARCHAR(36),
    goodsname VARCHAR(50),
    companyname VARCHAR(100)
);

-- 2. 建立子表临时表，用于暂存查询到的明细
CREATE TABLE #detail (
    orderseqno VARCHAR(36),
    detailid UNIQUEIDENTIFIER,
    unitprice DECIMAL(12,2),
    Qty int
);

-- 3. 向主表临时表插入分页数据
INSERT INTO #temp
SELECT * FROM (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY oi.createdate DESC) AS rownumber,
        oi.OrderSeqNO, 
        oi.GoodsName,
        ci.CompanyName
    FROM OrderInfo oi 
    INNER JOIN CompanyInfo ci ON oi.CompanyID = ci.CompanyID
    WHERE oi.CreateDate < GETDATE()
) AS oo
WHERE rownumber BETWEEN 10 AND 20;

-- 4. 【优化方案】直接连接子表获取全部明细，无需游标
-- 此处可根据需要插入到 #detail 或直接返回结果集
SELECT t.*, d.* 
FROM #temp t
LEFT JOIN OrderDetail d ON t.orderseqno = d.OrderSeqNO;

-- 5. 清理资源
DROP TABLE #temp;
DROP TABLE #detail;

/*
-- 以下为传统游标方案（仅供参考，性能较低）
DECLARE @temp_cursor CURSOR;
SET @temp_cursor = CURSOR FOR SELECT orderseqno FROM #temp;

DECLARE @orderseqno VARCHAR(36);
OPEN @temp_cursor;
FETCH NEXT FROM @temp_cursor INTO @orderseqno;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #detail
    SELECT OrderSeqNO, OrderDetailID, UnitPrice, Qty 
    FROM OrderDetail 
    WHERE OrderSeqNO = @orderseqno;
    
    FETCH NEXT FROM @temp_cursor INTO @orderseqno;
END

CLOSE @temp_cursor;
DEALLOCATE @temp_cursor;
*/