MySQL实现累计和范围数据筛选并额外返回后续一行的查询方法

适用MySQL 8.0+（支持窗口函数）的查询语句 #

默认按id升序排序计算累计和，如有其他排序需求可自行修改ORDER BY后的字段。

-- 自定义参数，可根据实际需求修改
SET @input_offset = 130;
SET @input_limit = 25;
SET @sum_upper_bound = @input_offset + @input_limit;

WITH cumulative_calculation AS (
    SELECT
        id,
        `count`,
        -- 按指定顺序计算累计和
        SUM(`count`) OVER (ORDER BY id ASC) AS cumulative_sum
    FROM 你的实际表名 -- 替换为自己的表名
)
SELECT id, `count`, cumulative_sum
FROM cumulative_calculation
WHERE
    -- 筛选累计和落在目标区间内的行
    cumulative_sum BETWEEN @input_offset AND @sum_upper_bound
    -- 额外返回区间结束后的第一行
    OR id = (
        SELECT MIN(id)
        FROM cumulative_calculation
        WHERE cumulative_sum > @sum_upper_bound
    )
ORDER BY id ASC;

适用MySQL 5.x（不支持窗口函数）的查询语句 #

通过用户变量实现累计和计算：

SET @input_offset = 130;
SET @input_limit = 25;
SET @sum_upper_bound = @input_offset + @input_limit;
SET @current_cum_sum = 0;
SET @hit_upper_bound = 0;

SELECT id, `count`, cumulative_sum
FROM (
    SELECT
        id,
        `count`,
        @current_cum_sum := @current_cum_sum + `count` AS cumulative_sum,
        CASE WHEN @current_cum_sum > @sum_upper_bound THEN @hit_upper_bound := 1 ELSE 0 END AS over_flag
    FROM 你的实际表名, (SELECT @current_cum_sum := 0) AS init -- 替换为自己的表名
    ORDER BY id ASC
) AS temp_data
WHERE
    cumulative_sum BETWEEN @input_offset AND @sum_upper_bound
    OR (over_flag = 1 AND cumulative_sum = (
        SELECT MIN(cs)
        FROM (
            SELECT @cum_sum_temp := @cum_sum_temp + `count` AS cs
            FROM 你的实际表名, (SELECT @cum_sum_temp := 0) AS init -- 替换为自己的表名
            ORDER BY id ASC
        ) AS temp_cum
        WHERE cs > @sum_upper_bound
    ))
ORDER BY id ASC;

注意说明 #

• count是MySQL保留关键字，查询时需要用反引号`包裹避免语法报错
• 如果你需要按除id外的其他字段排序计算累计和，修改两处ORDER BY id ASC为对应的排序规则即可
• 如果你需要和示例一致返回所有累计和大于等于offset的行，直接将WHERE条件替换为cumulative_sum >= @input_offset即可。

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