SQL按sort_column排序后按value_column累计和小于100分组实现方法

这个需求是典型的有序序列连续分组问题，要求严格按sort_column排序后切分连续行，每个分组的value_column总和尽可能大且小于100，下面给出两种主流实现方案：

方案1：递归CTE（通用兼容方案） #

适配 PostgreSQL、MySQL 8.0+、SQL Server、BigQuery 等所有支持递归CTE的数据库，兼容性最好，逻辑清晰可控：

WITH RECURSIVE ranked_rows AS (
    -- 先给所有行生成连续序号，避免sort_column断号影响递归逻辑
    SELECT 
        sort_column,
        value_column,
        ROW_NUMBER() OVER (ORDER BY sort_column) AS row_num
    FROM your_table
),
grouped_rows AS (
    -- 锚点：取排序后的第一行作为分组1的起点
    SELECT 
        row_num,
        sort_column,
        value_column,
        1 AS group_id,
        value_column AS running_sum
    FROM ranked_rows
    WHERE row_num = 1

    UNION ALL

    -- 递归处理后续每一行
    SELECT 
        r.row_num,
        r.sort_column,
        r.value_column,
        CASE 
            WHEN g.running_sum + r.value_column < 100 THEN g.group_id
            ELSE g.group_id + 1 
        END AS group_id,
        CASE 
            WHEN g.running_sum + r.value_column < 100 THEN g.running_sum + r.value_column
            ELSE r.value_column 
        END AS running_sum
    FROM grouped_rows g
    INNER JOIN ranked_rows r ON r.row_num = g.row_num + 1
)
SELECT 
    sort_column,
    value_column,
    group_id AS desired_result
FROM grouped_rows
ORDER BY sort_column;

逻辑说明 #

• 先通过ROW_NUMBER()生成连续行号，不管原表sort_column是否存在断号都能正常执行
• 递归锚点初始化第一行的分组号为1，分组累加和为第一行的value_column值
• 每处理下一行时判断：当前分组累加和加上当前行值如果小于100就归入当前分组，累加和叠加；否则新开分组，累加和重置为当前行值
• 执行结果和你给出的示例desired_result完全一致

方案2：用户变量（MySQL 5.x 专属方案） #

如果用的是不支持递归CTE的旧版本MySQL，可以用用户变量实现，写法更简洁：

SELECT 
    sort_column,
    value_column,
    group_id AS desired_result
FROM (
    SELECT 
        sort_column,
        value_column,
        @group_id := IF(@running_sum + value_column < 100, @group_id, @group_id + 1) AS group_id,
        @running_sum := IF(@running_sum + value_column < 100, @running_sum + value_column, value_column) AS running_sum
    FROM your_table
    CROSS JOIN (SELECT @group_id := 1, @running_sum := 0) AS init
    ORDER BY sort_column
) AS res;

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