在SQL Server中对父查询进行分页处理的方法（基于父/子查询）

Question

(SQL Server 2012 - Web Edition)

我在一个查询中有一个父子（一对多）关系，如下所示：

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

我有一个包含以下内容的大型分页查询，使用标准的（伪代码）：

WITH C as (SELECT top(@perpage*@pagenum) rowID = row_number() OVER (somefield)),
 SELECT c.* FROM C (query) WHERE DT_RowId > (@pagenum-1)*@perpage

在这种情况下，是否可以对父表（a）进行分页而不是整个查询？我能否修改我的分页查询（非检索查询本身的SQL），以便在请求10行时，它会从父表中给出10行，并附带'x'个子行？

我知道我没有提供更大的背景，但这个背景比较复杂。如果需要，我们可以谈及到那里，但它很复杂。以下是我们此次翻译的一部分内容。

    IF UPPER(LEFT(@rSQL, 6)) = 'SELECT'
        BEGIN
            SET @rSQL = 'SELECT * FROM (' + @rSQL + ')' + ' as rTBL';
            SET @rSQL = RIGHT(@rSQL, LEN(@rSQL)-7);
            IF (LEN(LTRIM(@search)) > 0)
                BEGIN
                    SET @rPaging = 
                        'IF (@schemaonly=1) SET FMTONLY ON; 
                        SELECT @ttlrows = COUNT(*) FROM (SELECT ' + @rSQL + @rWhere + ') AS TBL; 
                        WITH C as (select top(@perpage*@pagenum) DT_RowId = ROW_NUMBER() OVER (' + @rOrder + '), ';
                    SET @rPaging = @rPaging + @rSQL + @rWhere + ')  
                        SELECT C.*' + @rcols + ', (@perpage-1) * @pagenum as pagenum, @ttlrows as ct, CEILING(@ttlrows / CAST(@perpage AS FLOAT)) as pages 
                        FROM C '+ @query + ' WHERE DT_RowId > (@pagenum-1) * @perpage ';
                END
            ELSE
                BEGIN

                    SET @rPaging = 
                        'IF (@schemaonly=1) SET FMTONLY ON; 
                        SELECT @ttlrows = COUNT(*) FROM (' + @oSQL + ') AS SUBQUERY; 
                        WITH C as (select top(@perpage*@pagenum) DT_RowId = ROW_NUMBER() OVER (' + @rOrder + '), ';
                    SET @rPaging = @rPaging + @rSQL + ')  
                        SELECT C.*' + @rcols + ',(@perpage-1) * @pagenum as pagenum, @ttlrows as ct, CEILING(@ttlrows / CAST(@perpage AS FLOAT)) as pages 
                        FROM C '+ @query + ' WHERE DT_RowId > (@pagenum-1) * @perpage ';
                END
            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
    ELSE
        BEGIN
            SET @rSQL = LTRIM(REPLACE(UPPER(@rSQL), 'EXEC',''));
            EXECUTE SP_EXECUTESQL @rSQL, @parms, @ttlrows out, @schemaonly, @perpage, @pagenum, @fksiteID, @filter1, @filter2, @filter3, @filter4, @intfilter1, @intfilter2, @intfilter3, @intfilter4, @datefilter1, @datefilter2;
        END

- dudeinco

---

请问您能告诉我 SQL Server 的版本号吗？ - Rajat Jaiswal

SQL Server 2012（Web版） - dudeinco

Answer 1

您可以添加

标签。

,DENSE_RANK() OVER (ORDER BY table_a.primary_key)

这将间接地达到与之前相同的结果。

,ROW_NUMBER() OVER(ORDER BY table_a.primary_key) 

但是前者将出现在最终结果集中，而不是回到表a获取后者代码片段。

但请注意缺点：任何额外的排名函数都会在结果集上强制执行额外的排序操作！这可能会显着影响查询性能。如果您的情况是这种情况，请遵循Tab Allemans的解决方案并使用cte。

- Lmu92

Answer 2

你可以在一个只获取父行的公用表表达式（CTE）中进行分页，然后在随后的另一个CTE或主查询中连接子行。

由于你使用的方式是动态的，这可能需要使用与构建@query相同的基本元素来构建分页查询。如果没有看到构建@query的代码，我就不能再提供更具体的信息了。

- Tab Alleman

---

查询本身实际上是由一张表驱动的。因此，查询是预先构建的，并带有内置的预设（可预测）参数。 - dudeinco

这可能需要我重新思考如何处理查询，但这可能是唯一的方法？ - dudeinco

是的，您可能需要使用与构建查询相同的逻辑来构建查询的分页部分。 - Tab Alleman

如果您选择使用CTE路线，那么我建议使用TOP(@rows_required) ... ORDER BY而不是ROW_NUMBER() ... WHERE ROW_NUMBER() < = @rows_required。（代码片段极为简化 :-) ） - Lmu92

请注意，SQL Server 2012 在 ORDER BY 子句中添加了 OFFSET..FETCH 关键字，专门用于分页。 - Tab Alleman

我相信这是正确的答案，经过研究后，需要我重新思考一些事情，但它并没有像我想象的那么令人生畏。而且偏移和提取数据要简单得多...我也会研究一下这个。 - dudeinco