如何用SQL按name分组生成id组合并对对应totalAmount求和

要生成同name分组下的所有非单元素ID组合，用递归CTE就能实现，不需要提前手动拆分ID，逻辑如下：

通用思路 #

通过递归生成同分组下的所有ID子集，通过限制仅和更大的ID（或分组内序号）组合避免重复，最后过滤掉仅含单个ID的子集，直接输出结果即可。

PostgreSQL/MySQL 8.0+ 实现示例 #

假设你的表名为your_table，SQL代码如下：

WITH RECURSIVE combinations AS (
    -- 锚点层：每个单条记录作为初始子集
    SELECT 
        ARRAY[id] AS id_list,
        name,
        totalAmount AS sum_amount,
        id AS max_id
    FROM your_table
    UNION ALL
    -- 递归层：和同分组下更大的ID拼接生成新子集
    SELECT 
        c.id_list || t.id,
        c.name,
        c.sum_amount + t.totalAmount,
        t.id AS max_id
    FROM combinations c
    JOIN your_table t ON c.name = t.name AND t.id > c.max_id
)
-- 过滤非单元素组合，输出结果
SELECT 
    STRING_AGG(id::TEXT, ',' ORDER BY id) AS "id's",
    name,
    sum_amount AS totalAmount
FROM combinations
WHERE ARRAY_LENGTH(id_list, 1) >= 2
GROUP BY id_list, name, sum_amount
ORDER BY name, ARRAY_LENGTH(id_list, 1), "id's";

SQL Server 实现示例 #

WITH combinations AS (
    SELECT 
        CAST(id AS VARCHAR(100)) AS id_str,
        name,
        totalAmount AS sum_amount,
        id AS max_id
    FROM your_table
    UNION ALL
    SELECT 
        CONCAT(c.id_str, ',', t.id),
        c.name,
        c.sum_amount + t.totalAmount,
        t.id AS max_id
    FROM combinations c
    JOIN your_table t ON c.name = t.name AND t.id > c.max_id
)
SELECT 
    id_str AS "id's",
    name,
    sum_amount AS totalAmount
FROM combinations
WHERE LEN(id_str) - LEN(REPLACE(id_str, ',', '')) + 1 >= 2
ORDER BY name, LEN(id_str), id_str;

说明 #

• 逻辑中用id > max_id的限制条件，避免生成重复的无序组合，比如只会出现1,2不会出现2,1
• 如果你的ID不是递增规则，可以先给每个name分组内的记录生成递增行号，用行号代替ID做组合判断，逻辑完全一致
• 方案支持任意数量的同分组ID，不需要提前知道每个分组的ID个数

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