基于A、B两列累加值计算分页页码的SQL实现需求

规则适配说明 #

你需求中的A列对应现有SQL输出的Contentcount字段，B列对应ChildContentcount字段，我们会按照指定排序规则（置顶优先级降序、评论创建时间升序）逐行累加两列之和，单页累计值满15时，当前行归入当前页，下一行启用新页码重新累计。

最终SQL语句 #

WITH OriginalData AS (
    -- 基础数据集：保留原有查询逻辑，新增行序号保证累加顺序正确
    SELECT 
        IIF(c2.isdeleted = 1 OR c2.approved = 0, NULL, c2.content) AS Content,
        (SELECT STRING_AGG(c1.content, ', ') 
         FROM comments c1 
         WHERE c1.parentcommentid = c2.id 
           AND c1.isdeleted = 0 
           AND c1.approved = 1) ChildContent,
        IIF(c2.isdeleted = 1 OR c2.approved = 0, 0, 1) AS Contentcount,
        (SELECT COUNT(c1.content) 
         FROM comments c1 
         WHERE c1.parentcommentid = c2.id 
           AND c1.isdeleted = 0 
           AND c1.approved = 1) ChildContentcount,
        ROW_NUMBER() OVER(ORDER BY c2.pinned DESC, c2.createddate) AS rn
    FROM 
        comments c2
    WHERE  
        c2.discussionid = '402930'
        AND c2.parentcommentid IS NULL
),
RecursivePage AS (
    -- 递归起始行：处理第一行数据
    SELECT 
        Content,
        ChildContent,
        Contentcount,
        ChildContentcount,
        rn,
        1 AS PageNumber,
        15 - (Contentcount + ChildContentcount) AS RemainingCapacity
    FROM OriginalData
    WHERE rn = 1
    UNION ALL
    -- 递归计算后续每行的页码
    SELECT 
        od.Content,
        od.ChildContent,
        od.Contentcount,
        od.ChildContentcount,
        od.rn,
        CASE WHEN rp.RemainingCapacity < (od.Contentcount + od.ChildContentcount) THEN rp.PageNumber + 1 ELSE rp.PageNumber END AS PageNumber,
        CASE WHEN rp.RemainingCapacity < (od.Contentcount + od.ChildContentcount) THEN 15 - (od.Contentcount + od.ChildContentcount) ELSE rp.RemainingCapacity - (od.Contentcount + od.ChildContentcount) END AS RemainingCapacity
    FROM OriginalData od
    INNER JOIN RecursivePage rp ON od.rn = rp.rn + 1
)
-- 输出最终结果，页码按要求格式化
SELECT 
    Content,
    ChildContent,
    Contentcount,
    ChildContentcount,
    CONCAT('Page ', PageNumber) AS Pagenumber
FROM RecursivePage
ORDER BY rn

验证说明 #

上述SQL逻辑与你给出的样例计算规则完全匹配，实际执行后输出的页码结果与预期输出一致。

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