在SQL中实现物品按容器容量动态分配

核心思路 #

先为每个容器计算可容纳物品的序号区间（比如容量2的容器对应1-2，容量5的对应3-7，容量10的对应8-17），再给物品按顺序生成连续序号，最后通过序号区间匹配对应的容器完成分配。

步骤1：预处理容器，生成容量区间 #

对容器按分配顺序（示例按containerID排序，可按需调整）计算每个容器的起始、结束序号：

WITH container_ranges AS (
    SELECT 
        containerID,
        capacity,
        -- 当前容器的起始序号：前序容器累计容量 + 1
        SUM(capacity) OVER (ORDER BY containerID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) + 1 AS start_num,
        -- 当前容器的结束序号：累计到当前容器的总容量
        SUM(capacity) OVER (ORDER BY containerID) AS end_num
    FROM containers
)
SELECT * FROM container_ranges;

输出示例：

步骤2：为物品生成连续序号 #

给物品按指定顺序（示例按itemID排序）生成行号：

WITH item_with_num AS (
    SELECT 
        itemID,
        ROW_NUMBER() OVER (ORDER BY itemID) AS item_num
    FROM items
)
SELECT * FROM item_with_num;

步骤3：匹配并更新物品的容器ID #

结合上述两个临时结果集，通过序号区间匹配容器，更新items表：

WITH container_ranges AS (
    SELECT 
        containerID,
        SUM(capacity) OVER (ORDER BY containerID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) + 1 AS start_num,
        SUM(capacity) OVER (ORDER BY containerID) AS end_num
    FROM containers
),
item_with_num AS (
    SELECT 
        itemID,
        ROW_NUMBER() OVER (ORDER BY itemID) AS item_num
    FROM items
)
UPDATE items
SET containerID = cr.containerID
FROM items i
JOIN item_with_num iwn ON i.itemID = iwn.itemID
JOIN container_ranges cr ON iwn.item_num BETWEEN cr.start_num AND cr.end_num;

额外说明 #

• 若容器分配顺序不是containerID，需把ORDER BY containerID替换为实际优先级字段（比如新增的sort_order字段）。
• 若物品总数超过容器总容量，超出部分可保留containerID为空，只需修改UPDATE语句的条件：

UPDATE items
SET containerID = cr.containerID
FROM items i
JOIN item_with_num iwn ON i.itemID = iwn.itemID
JOIN container_ranges cr ON iwn.item_num BETWEEN cr.start_num AND cr.end_num
WHERE cr.containerID IS NOT NULL;

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