SQL Server累计和达标节点定位及批量分组SQL/算法实现问询

当然可以搞定！这种分组逻辑其实是滚动累计和分组的一个变种，在SQL Server里用窗口函数或者递归CTE就能轻松实现，完全不用写复杂的循环。下面我一步步给你拆解思路和代码：

先明确需求逻辑 #

咱们再对齐下你的需求：

• 给定已排序的数字列表，从第一行开始累加；
• 所有累加和≤阈值k（这里是10）的行归为一组，BatchValue=0；
• 累加超过阈值后，剩余的每一行单独作为一个分组，BatchValue依次为1、2、3...

方案1：用窗口函数快速实现（简洁高效） #

这种方法适合你的场景，代码短且性能不错，前提是你的数据已经是预排序的（如果有主键/排序字段，一定要用实际字段代替(SELECT NULL)）。

测试数据准备 #

先创建测试表方便验证：

CREATE TABLE YourTable (Quantity INT);
INSERT INTO YourTable VALUES (2), (4), (4), (6), (8);

实现查询 #

-- 定义阈值k，可根据需求修改
DECLARE @k INT = 10;

WITH CumulativeSum AS (
    SELECT 
        Quantity,
        -- 计算从第一行到当前行的累计和
        SUM(Quantity) OVER (ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
    FROM YourTable
),
GroupedResult AS (
    SELECT 
        Quantity,
        CASE 
            -- 累计和≤k的行归为0组
            WHEN RunningTotal <= @k THEN 0
            -- 超出的行按顺序编号，从1开始
            ELSE ROW_NUMBER() OVER (PARTITION BY CASE WHEN RunningTotal <= @k THEN 0 ELSE 1 END ORDER BY (SELECT NULL))
        END AS BatchValue
    FROM CumulativeSum
)
SELECT Quantity, BatchValue
FROM GroupedResult;

结果验证 #

执行后会得到和你示例完全一致的结果：

注意事项 #

• 如果你的数据有明确的排序字段（比如ID、CreateTime），一定要把ORDER BY (SELECT NULL)换成实际字段，否则排序可能不可靠；
• 阈值@k可以动态修改，适配不同场景。

方案2：递归CTE（更灵活的通用方案） #

如果以后你的需求有变化（比如允许拆分行、或者剩余容量还能容纳下一行），递归CTE的方法会更灵活，它能逐行判断是否加入当前组。

实现代码 #

DECLARE @k INT = 10;

WITH RecursiveCTE AS (
    -- 初始化：处理第一行
    SELECT 
        Quantity,
        Quantity AS CurrentGroupTotal,
        0 AS BatchValue,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM YourTable
    UNION ALL
    -- 递归处理后续行
    SELECT 
        y.Quantity,
        -- 判断当前行能否加入上一个分组
        CASE 
            WHEN r.CurrentGroupTotal + y.Quantity <= @k THEN r.CurrentGroupTotal + y.Quantity
            ELSE y.Quantity
        END AS CurrentGroupTotal,
        -- 确定当前行的分组编号
        CASE 
            WHEN r.CurrentGroupTotal + y.Quantity <= @k THEN r.BatchValue
            ELSE r.BatchValue + 1
        END AS BatchValue,
        y.RowNum
    FROM YourTable y
    JOIN RecursiveCTE r ON y.RowNum = r.RowNum + 1
)
SELECT Quantity, BatchValue
FROM RecursiveCTE
ORDER BY RowNum;

逻辑说明 #

• 从第一行开始，维护当前分组的累计和CurrentGroupTotal；
• 如果下一行加入后累计和不超过@k，就留在当前分组；
• 如果超过阈值，就新建一个分组，累计和重置为当前行的数值，分组编号+1；
• 这种方法同样能完美匹配你的需求，而且扩展性更强。

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