SQL中按需求量筛选地址：基于数量总和限制结果

假设表结构 #

先明确两个核心表的结构（可根据实际业务调整字段名）：

• item_requirements：存储各物品的总需求量

  item_id	required_qty
  Item A	20
  Item B	5
  Item C	23

• address_inventory：存储各地址的物品库存

  address_id	item_id	available_qty
  Address 1	Item A	15
  Address 2	Item A	10
  Address 3	Item A	10
  Address 4	Item A	13

单一物品的筛选方案（以Item A为例） #

如果仅需处理单个物品的需求，可通过计算累计库存定位刚好满足需求的地址组合：

WITH ranked_inventory AS (
    SELECT
        address_id,
        item_id,
        available_qty,
        -- 按库存从小到大排序后计算累计值（可根据优先级调整排序规则）
        SUM(available_qty) OVER (PARTITION BY item_id ORDER BY available_qty) AS cumulative_qty
    FROM address_inventory
    WHERE item_id = 'Item A'
),
required_total AS (
    SELECT required_qty FROM item_requirements WHERE item_id = 'Item A'
)
SELECT
    ri.address_id,
    ri.item_id,
    ri.available_qty
FROM ranked_inventory ri
JOIN required_total rt ON ri.cumulative_qty <= rt.required_qty
-- 确保最终累计库存能覆盖需求
WHERE (SELECT MAX(cumulative_qty) FROM ranked_inventory) >= rt.required_qty;

多物品批量处理方案 #

若需同时处理多个物品的需求，可通过分区窗口函数实现按物品分组计算：

WITH ranked_inventory AS (
    SELECT
        address_id,
        item_id,
        available_qty,
        SUM(available_qty) OVER (PARTITION BY item_id ORDER BY available_qty) AS cumulative_qty
    FROM address_inventory
),
item_requirements_with_cumulative AS (
    SELECT
        ri.address_id,
        ri.item_id,
        ri.available_qty,
        ri.cumulative_qty,
        ir.required_qty
    FROM ranked_inventory ri
    JOIN item_requirements ir ON ri.item_id = ir.item_id
)
SELECT
    address_id,
    item_id,
    available_qty
FROM item_requirements_with_cumulative
WHERE cumulative_qty <= required_qty
AND EXISTS (
    SELECT 1
    FROM item_requirements_with_cumulative irwc
    WHERE irwc.item_id = item_requirements_with_cumulative.item_id
    GROUP BY irwc.item_id
    HAVING MAX(irwc.cumulative_qty) >= MAX(irwc.required_qty)
);

关键说明 #

• 排序规则：示例按available_qty从小到大排序，优先用小批量库存凑够总量；若需优先选大批量，可改为ORDER BY available_qty DESC。
• 累计逻辑：通过窗口函数SUM() OVER()计算累计库存，保证筛选出的地址库存之和刚好覆盖（或不超过）需求总量。
• 多物品适配：利用PARTITION BY item_id实现按物品分组计算，满足多物品批量处理需求。

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