寻求最优箱组合SQL方案：用最少箱子满足任意产品数量订单

业务场景与示例数据 #

多种产品按不同数量分装在带唯一ID的箱子中。示例订单需3件Product A、6件Product B、15件Product C，对应数据库表结构及数据如下：

CREATE TABLE Orders (
    order_id INT,
    product VARCHAR(255),
    quantity INT
);

INSERT INTO Orders (order_id, product, quantity) VALUES
(101, 'Product A', 3),
(101, 'Product B', 6),
(101, 'Product C', 15);


CREATE TABLE Boxes (
    box_id INT,
    product VARCHAR(255),
    quantity INT
);

INSERT INTO Boxes (box_id, product, quantity) VALUES
(1, 'Product A', 2), (1, 'Product B', 3), (1, 'Product C', 4),
(2, 'Product A', 2), (2, 'Product B', 6), (2, 'Product C', 1),
(3, 'Product A', 2), (3, 'Product B', 3), (3, 'Product C', 4),
(4, 'Product A', 0), (4, 'Product B', 0), (4, 'Product C', 9),
(5, 'Product A', 0), (5, 'Product B', 3), (5, 'Product C', 6);

该示例订单的最优解为选择箱子1、3、4，仅需打开3个箱子即可完成订单需求。

现有问题 #

我编写的存储过程FulfillOrderComplex在产品数量超过500时失效，代码如下：

CREATE OR ALTER PROCEDURE FulfillOrderComplex
AS
BEGIN
    DECLARE @RemainingA INT, @RemainingB INT, @RemainingC INT;
    SET @RemainingA = 3; -- Example order requirements
    SET @RemainingB = 6;
    SET @RemainingC = 15;

    -- Temporary table to track box selections
    CREATE TABLE #SelectedBoxes (box_id INT);

    -- Logic to select boxes
    WHILE (@RemainingA > 0 OR @RemainingB > 0 OR @RemainingC > 0)
    BEGIN
        -- Find the box that maximizes the reduction of remaining quantities
        DECLARE @BestBox INT;
        SELECT TOP 1 @BestBox = box_id FROM Boxes
        WHERE (quantity >= @RemainingA AND product = 'Product A') OR
              (quantity >= @RemainingB AND product = 'Product B') OR
              (quantity >= @RemainingC AND product = 'Product C')
        ORDER BY (CASE WHEN product = 'Product A' THEN @RemainingA - quantity
                       WHEN product = 'Product B' THEN @RemainingB - quantity
                       WHEN product = 'Product C' THEN @RemainingC - quantity END);

        -- Update remaining requirements
        UPDATE @RemainingA, @RemainingB, @RemainingC based on @BestBox contents;

        -- Add @BestBox to selected boxes
        INSERT INTO #SelectedBoxes VALUES (@BestBox);

        -- Avoid selecting the same box again
        DELETE FROM Boxes WHERE box_id = @BestBox;
    END

    -- Return the selected boxes
    SELECT box_id FROM #SelectedBoxes;
END;

需求 #

恳请协助编写SQL查询，返回满足订单所需最少的箱子ID，且适配任意数量产品的订单。

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