MySQL如何根据指定行对应列的累计和实现条件筛选查询

假设我们有如下结构的数据表：

id   other_id   limit   
......................
1     1          4
2     1          5
3     2          3
4     2          2

需求：给定总限制值total_limit和指定other_id筛选条件，按id升序累加limit字段值，返回所有匹配规则的行ID，规则示例如下：

• total_limit = 3, other_id = 1 => 返回行ID 1
• total_limit = 9, other_id = 1 => 返回行ID 1,2
• total_limit = 10, other_id = 1,2 => 返回行ID 1,2,3
• total_limit = 15, other_id = 1,2 => 返回行ID 1,2,3,4
• total_limit = 3, other_id = 2 => 返回行ID 3
• total_limit = 4, other_id = 2 => 返回行ID 3,4

limit是SQL关键字，查询时需要用反引号包裹避免语法报错，以下是不同数据库场景的实现：

支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、Oracle等） #

核心逻辑是先按id升序计算累积和，筛选出「当前行之前的累加和小于total_limit」的所有行，刚好完全匹配你的规则：

-- 直接返回多行ID的写法，示例参数：total_limit=10，筛选other_id为1、2
SELECT id
FROM (
    SELECT 
        id,
        `limit`,
        SUM(`limit`) OVER (ORDER BY id ASC) AS cumulative_limit
    FROM 你的表名
    WHERE other_id IN (1,2)
) t
WHERE cumulative_limit - `limit` < 10;

如果需要把结果拼接为逗号分隔的字符串，修改外层查询即可：

SELECT GROUP_CONCAT(id ORDER BY id) AS result_ids
FROM (
    SELECT 
        id,
        `limit`,
        SUM(`limit`) OVER (ORDER BY id ASC) AS cumulative_limit
    FROM 你的表名
    WHERE other_id IN (1,2)
) t
WHERE cumulative_limit - `limit` < 10;

低版本MySQL（不支持窗口函数） #

用自定义变量实现累积和计算：

SET @cumulative := 0;
SELECT id
FROM (
    SELECT 
        id,
        `limit`,
        @cumulative := @cumulative + `limit` AS cumulative_limit
    FROM 你的表名
    WHERE other_id IN (1,2)
    ORDER BY id ASC
) t
WHERE cumulative_limit - `limit` < 10;

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