如何使用SQL将指定硬件的待分配数量匹配到现有可用盒子库存

核心思路是对目标硬件的盒子按指定分配顺序做累计库存求和，通过累计值与待分配总量的对比直接计算每个盒子的分配量，无需循环，查询效率更高。

MySQL 8.0+ 版本（支持窗口函数） #

-- 入参定义
SET @hardwareId = 5;
SET @quantity = 9;

SELECT 
    HWID,
    BoxId,
    LEAST(Quantity, @quantity - GREATEST(0, cumulative_sum - Quantity)) AS allocated_quantity
FROM (
    SELECT 
        HWID,
        BoxId,
        Quantity,
        -- 按BoxId升序累计计算库存，可根据业务调整分配顺序（如大盒优先改ORDER BY规则即可）
        SUM(Quantity) OVER (PARTITION BY HWID ORDER BY BoxId ASC) AS cumulative_sum
    FROM temp_Boxes
    WHERE HWID = @hardwareId
) t
-- 过滤出需要参与分配的盒子
WHERE cumulative_sum - Quantity < @quantity;

针对你给出的示例参数，该查询返回结果如下，完全符合预期：

如果待分配总量小于总库存，会自动计算最后一个盒子的截取量，比如待分配7时，返回结果为(5,1,3)、(5,2,4)；如果待分配总量超过总库存，会自动返回所有盒子的全部可用库存。

MySQL 5.x 兼容版本（无窗口函数支持） #

通过用户变量实现累计求和，逻辑和上述方案一致：

-- 入参定义
SET @hardwareId = 5;
SET @quantity = 9;
SET @cumulative_sum = 0;

SELECT 
    HWID,
    BoxId,
    LEAST(Quantity, @quantity - GREATEST(0, cumulative_sum - Quantity)) AS allocated_quantity
FROM (
    SELECT 
        HWID,
        BoxId,
        Quantity,
        @cumulative_sum := @cumulative_sum + Quantity AS cumulative_sum
    FROM temp_Boxes
    WHERE HWID = @hardwareId
    ORDER BY BoxId ASC
) t
WHERE cumulative_sum - Quantity < @quantity;

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