如何按订单中产品ID的所有组合聚合求和结果？

我完全理解你的需求——要针对每个订单ID，生成其下产品ID的所有可能非空组合，再对每个组合执行求和聚合操作。这个需求可以通过递归CTE（公共表表达式）来实现，下面我结合常见数据库的语法，一步步给你拆解解决方案：

第一步：先明确表结构（基于你的示例截图） #

假设你的表名为order_items，结构大概如下（包含核心的订单、产品、待聚合数值列）：

我们的目标是，比如对Order 1生成所有产品组合的求和结果：

• {A} → 总金额10
• {B} → 总金额20
• {C} → 总金额15
• {A,B} → 总金额30
• {A,C} → 总金额25
• {B,C} → 总金额35
• {A,B,C} → 总金额45

第二步：用递归CTE生成所有组合（适配PostgreSQL/SQL Server/MySQL 8.0+） #

代码示例： #

WITH RECURSIVE product_combinations AS (
    -- 基准场景：单个产品的基础组合
    SELECT
        Order_Id,
        CAST(Product_Id AS VARCHAR(100)) AS Product_Combination,
        Product_Id,
        Amount AS Total_Amount
    FROM order_items

    UNION ALL

    -- 递归步骤：将现有组合与后续产品拼接，生成新组合
    SELECT
        oc.Order_Id,
        CONCAT(oc.Product_Combination, ', ', oi.Product_Id) AS Product_Combination,
        oi.Product_Id,
        oc.Total_Amount + oi.Amount AS Total_Amount
    FROM product_combinations oc
    JOIN order_items oi
        ON oc.Order_Id = oi.Order_Id
        AND oi.Product_Id > oc.Product_Id -- 避免生成重复组合（比如A,B和B,A视为同一组合）
)
-- 最终输出：按订单、组合长度排序，展示结果
SELECT
    Order_Id,
    Product_Combination,
    Total_Amount
FROM product_combinations
ORDER BY Order_Id, LENGTH(Product_Combination), Product_Combination;

关键细节说明： #

• oi.Product_Id > oc.Product_Id：这一步是核心，用来避免生成顺序不同的重复组合（比如先A后B和先B后A）。如果你的Product_Id是数字类型，直接按数值大小比较即可；如果是字符串，会按字典序比较。
• 如果需要包含空组合（即不选任何产品的情况），可以在基准场景里额外添加一条每个Order_Id的空组合记录，Total_Amount设为0。
• MySQL用户注意：必须是8.0及以上版本才支持递归CTE，语法和上面一致。

第三步：针对数字类型Product_Id的调整 #

如果你的Product_Id是整数类型，只需要调整字符串转换的部分即可：

-- 基准场景里的组合字段
CAST(Product_Id AS VARCHAR) AS Product_Combination
-- 递归步骤里的拼接字段
CONCAT(oc.Product_Combination, ', ', CAST(oi.Product_Id AS VARCHAR))

常见问题排查 #

1. 出现重复组合：大概率是递归时没加oi.Product_Id > oc.Product_Id这个条件，导致生成了顺序不同的同组合。
2. 求和结果错误：检查递归时的求和逻辑，确保是把现有组合的总和加上新加入产品的数值，而非重新计算。
3. 数据库不支持递归CTE：如果是MySQL 5.x这类旧版本，建议升级数据库，或者用存储过程、笛卡尔积方式实现（但后者效率较低）。

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