SQL Server查询实现订单数量最优拆分 选取最小商品取货件数

我们需要实现给定订单需求量，优先选择大包装以保证总取货件数最少，同时校验库存限制的查询逻辑，具体实现如下：

核心思路 #

采用贪心算法逻辑，按包装大小从大到小排序，依次计算每个包装可取的最大数量，扣减剩余需求量，直到需求被完全满足，保证总件数最少。

完整查询代码 #

-- 定义输入参数：订单需求量
DECLARE @OrderQty INT = 7;

WITH RankedPacks AS (
    -- 过滤有有效库存的商品，按包装大小倒序排序，优先处理大包装
    SELECT 
        Pid,
        Packsize,
        Quantity,
        ROW_NUMBER() OVER(ORDER BY Packsize DESC) AS rn
    FROM ProductPack
    WHERE Quantity > 0
),
RecursiveAllocation AS (
    -- 递归起点：处理最大的包装
    SELECT 
        rn,
        Pid,
        Packsize,
        Quantity AS RemainingStock,
        @OrderQty AS RemainingDemand,
        -- 计算当前包装最多可取数量，不能超过库存也不能超过需求需要的数量
        CASE 
            WHEN @OrderQty <= 0 THEN 0
            ELSE LEAST(Quantity, @OrderQty / Packsize)
        END AS TakeQty
    FROM RankedPacks
    WHERE rn = 1

    UNION ALL

    -- 递归处理后续更小的包装
    SELECT 
        rp.rn,
        rp.Pid,
        rp.Packsize,
        rp.Quantity AS RemainingStock,
        ra.RemainingDemand - (ra.TakeQty * ra.Packsize) AS RemainingDemand,
        CASE 
            WHEN ra.RemainingDemand - (ra.TakeQty * ra.Packsize) <= 0 THEN 0
            ELSE LEAST(rp.Quantity, (ra.RemainingDemand - (ra.TakeQty * ra.Packsize)) / rp.Packsize)
        END AS TakeQty
    FROM RecursiveAllocation ra
    JOIN RankedPacks rp ON rp.rn = ra.rn + 1
    WHERE ra.RemainingDemand > 0
)
-- 输出取货数量大于0的结果
SELECT Pid, TakeQty AS 取货数量
FROM RecursiveAllocation
WHERE TakeQty > 0
ORDER BY Pid;

注意：如果使用SQL Server 2022之前的版本，不支持LEAST函数，可将代码中的LEAST(x,y)替换为CASE WHEN x < y THEN x ELSE y END即可正常运行。

样例测试验证 #

样例1：库存充足场景 #

库存数据：

测试结果：

• 订单量=5时输出：

• 订单量=7时输出：

样例2：缺货场景 #

库存数据：

注：原缺货样例中Pid3库存标注为0与输出矛盾，此处调整为1匹配给定输出
测试结果：

• 订单量=5时输出：

• 订单量=7时输出：

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