SQL Server 2012中父子关联查询如何基于父表实现分页？

没问题，咱们来搞定这个分页需求——你现在的关联查询分页是把父+子的所有行混在一起分页，导致没法拿到指定数量的父记录及其全部子项。核心思路是先给父表做分页，拿到目标页的父主键集合，再关联子表拉取这些父记录的所有子数据，而且可以尽量不改动你原有的核心查询语句。

问题回顾 #

你当前的关联查询：

SELECT a.a, a.b, b.c FROM tablea INNER JOIN tableb ON b.pk = a.fk

现有分页逻辑是对整个关联结果集做行号分页，这会把同一个父表的多条子记录当成独立行，比如一个父有3个子记录，分页取10行的话，可能只拿到7个父记录（3+7=10），完全不符合你的需求。

解决方案：先分页父表，再关联子表 #

核心逻辑修改思路 #

我们要把分页的粒度从“关联后的所有行”切换到“父表记录”，步骤很清晰：

1. 先对父表tablea应用分页逻辑，拿到当前页的父主键列表；
2. 用这些父主键过滤原关联查询，得到这些父记录对应的所有子记录；
3. 同时调整总记录数的统计，从统计关联后的行数改成统计父表的总记录数。

适配你现有动态分页代码的修改 #

针对你提供的动态SQL代码片段，我们可以调整CTE部分的逻辑，下面是修改后的关键代码块（你需要根据实际表结构替换占位符）：

IF UPPER(LEFT(@rSQL, 6)) = 'SELECT' 
BEGIN
    -- 替换@parentPK为tablea的实际主键字段，比如a_id
    DECLARE @parentPK NVARCHAR(50) = 'a_id'; 
    DECLARE @parentCountSQL NVARCHAR(MAX);
    DECLARE @parentPagingSQL NVARCHAR(MAX);

    -- 统计父表总记录数（复用原WHERE条件）
    SET @parentCountSQL = N'SELECT @ttlrows = COUNT(*) FROM tablea ' + @rWhere;

    -- 先分页父表，拿到当前页的父主键和行号
    SET @parentPagingSQL = N'WITH ParentCTE AS (
        SELECT TOP(@perpage*@pagenum) 
            ' + @parentPK + ',
            DT_RowId = ROW_NUMBER() OVER (' + @rOrder + ')
        FROM tablea ' + @rWhere + '
    )
    -- 关联原查询的结果，只保留当前页父记录的所有子项
    SELECT p.DT_RowId, r.*
    FROM ParentCTE p
    INNER JOIN (' + @rSQL + ') r ON r.' + @parentPK + ' = p.' + @parentPK + '
    WHERE p.DT_RowId > (@pagenum-1)*@perpage';

    -- 整合到原分页逻辑中
    SET @rPaging = N'IF (@schemaonly=1) SET FMTONLY ON; 
    -- 执行父表总数统计
    EXEC sp_executesql @parentCountSQL, @parms, @ttlrows out, @schemaonly, @perpage, @pagenum, @fksiteID, @filter1, @filter2, @filter3, @filter4, @intfilter1, @intfilter2, @intfilter3, @intfilter4, @datefilter1, @datefilter2, @search;
    -- 执行分页查询
    ' + @parentPagingSQL + N'
    -- 返回分页元数据
    UNION ALL
    SELECT NULL, NULL, NULL, NULL, (@perpage-1)*@pagenum as pagenum, @ttlrows as ct, CEILING(@ttlrows / CAST(@perpage AS FLOAT)) as pages
    WHERE @schemaonly = 0'; -- 只在非schema模式下返回元数据

    PRINT @rPaging;
    EXECUTE SP_EXECUTESQL @rPaging, @parms, @ttlrows out, @schemaonly, @perpage, @pagenum, @fksiteID, @filter1, @filter2, @filter3, @filter4, @intfilter1, @intfilter2, @intfilter3, @intfilter4, @datefilter1, @datefilter2, @search;
    SET FMTONLY OFF;
END

关键修改点说明 #

• 分页粒度切换：通过ParentCTE先对父表分页，确保每次拿到@perpage数量的父记录（除非是最后一页），再关联原查询的结果，保证每个父的所有子项都被返回。
• 复用原查询：原@rSQL完全不用修改，只是把它当成子查询，用父分页的主键过滤，这样最大化保留你原有的业务逻辑。
• 总数统计修正：原来统计的是关联后的总行数，现在改成统计父表的总记录数，这样分页的总页数才是基于父记录的数量，符合你的需求。
• 排序注意事项：如果原@rOrder里包含子表的字段，你需要调整成父表的字段，因为分页是基于父表的，子表字段排序会导致父表分页顺序混乱。比如原排序是ORDER BY b.c，要改成ORDER BY a.a或者其他父表字段。

测试建议 #

• 测试第一页：比如@perpage=10，检查是否返回10个父记录的所有子项；
• 测试最后一页：当父表总记录数不是@perpage的整数倍时，检查是否返回剩余的父记录及其子项；
• 验证过滤条件：确保原@rWhere的过滤规则在父表分页时生效，返回的父记录符合业务要求。

内容的提问来源于stack exchange，提问作者dudeinco