如何编写SQL语句筛选capacity总和接近100的行？

问题背景 #

现有表结构如下：

需求是筛选出若干行，使得它们的capacity字段总和尽可能接近100，期望输出id为1和2的两行（总和118，是最接近100的组合）。

之前尝试按id分组并使用HAVING SUM(capacity)>=100无结果，原因是按id分组后每个组仅包含一行，单条行的capacity均小于100，因此条件无法命中。

解决方案 #

这本质是子集和问题，可以通过递归CTE生成所有可能的行组合，计算组合总和后筛选出最接近目标值的组合。以下是适配场景的SQL语句：

WITH RECURSIVE combinations AS (
    -- 初始化：每个单独行作为一个基础组合
    SELECT 
        id,
        date,
        capacity,
        depot,
        reserved,
        order_id,
        capacity AS total_capacity,
        CAST(id AS VARCHAR) AS included_ids
    FROM your_table
    WHERE depot = 1 AND date = '2024-04-29' -- 按需过滤目标仓库和日期
    UNION ALL
    -- 递归生成新组合：将后续行加入已有组合，避免重复
    SELECT 
        t.id,
        t.date,
        t.capacity,
        t.depot,
        t.reserved,
        t.order_id,
        c.total_capacity + t.capacity AS total_capacity,
        CONCAT(c.included_ids, ',', t.id) AS included_ids
    FROM combinations c
    JOIN your_table t ON t.id > c.id
    WHERE c.total_capacity + t.capacity <= 100 + 50 -- 限制总和上限，减少无效组合
)
-- 排序筛选最接近100的组合
SELECT 
    t.*
FROM your_table t
JOIN (
    SELECT 
        included_ids,
        total_capacity,
        ROW_NUMBER() OVER (
            ORDER BY 
                -- 优先选与100差值最小的，差值相同选总和更大的
                ABS(total_capacity - 100),
                total_capacity DESC
        ) AS rn
    FROM combinations
) ranked ON FIND_IN_SET(t.id, ranked.included_ids)
WHERE ranked.rn = 1;

关键说明 #

1. 递归CTE逻辑：生成所有不重复的行组合（通过t.id > c.id避免1+2和2+1这类重复组合），同时计算每个组合的总容量和包含的id列表。
2. 排序规则：先按与100的差值从小到大排序，差值相同则选总和更大的组合；若需要优先选择总和≥100的组合，可修改排序逻辑为：

   ORDER BY 
       CASE WHEN total_capacity >= 100 THEN 0 ELSE 1 END,
       ABS(total_capacity - 100)
   

3. 性能注意：该方法仅适合小数据量场景，若表行数较多，递归会生成大量组合，导致性能下降，此时建议结合业务限制（如限制组合最大行数）优化。

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