SQL实现商品库存按货架行容量分配摆放需求求助

现有数据表定义 #

1. 商品库存表@ProductTb #

存储各商品的库存数量：

Declare @ProductTb AS Table(
                            ProductId Int IDENTITY(1,1), 
                            Amount Int -- 商品库存数量
                            )
Insert Into @ProductTb
    Values
        (1600),
        (2000),
        (3200),
        (3000),
        (3100),
        (1300),
        (1600),
        (3000),
        (500),
        (300),
        (6000),
        (5000),
        (1500),
        (1100)

2. 货架信息表@ShelfTb #

存储货架的单行列容量和总行数：

Declare @ShelfTb As Table(
                            ShelfId Int IDENTITY(1,1), -- 货架唯一编号
                            Capacity Int,  -- 货架单行列容量
                            NumberOfShelf TinyInt -- 货架总行数
                            )

Insert Into @ShelfTb
    Values
        (9500,12),
        (8500,8),
        (10000,15)

3. 货架行列表@ShelfList #

通过CTE生成所有货架的行记录：

Declare @ShelfList As Table(
                            Id Int IDENTITY(1,1),
                            ShelfId Int, -- 货架唯一编号
                            Capacity Int,  -- 该行容量
                            RowNumber Int -- 货架行号
                            )
;
With Cte1
AS
(
    Select
            s.[ShelfId],
            s.[Capacity],
            1 RowNumber
        From @ShelfTb As s
    Union All
    Select
            c.[ShelfId],
            c.[Capacity],
            c.[RowNumber] + 1
        From Cte1 As c
        Inner Join @ShelfTb As s On s.[ShelfId] = c.[ShelfId]
        Where   c.[RowNumber] < s.[NumberOfShelf]

)
Insert Into @ShelfList
Select *
    From Cte1 As c

需求说明 #

实现商品库存按货架行容量依次分配摆放，当某一行容量填满后自动切换到下一行。

原尝试代码问题 #

你之前的代码使用笛卡尔积关联两张表，仅对每个货架行的商品求和，并未实现按容量分配的核心逻辑：

Select
        *,
        Sum(p.[Amount]) Over (Partition By s.[ShelfId], s.[RowNumber] Order By s.[ShelfId], s.[RowNumber], p.[ProductId]) SumAmount

    From @ShelfList As s, @ProductTb As p
    Order By s.[Id], s.[ShelfId], s.[RowNumber]

正确实现方案 #

核心思路是计算商品的累计库存总量，同时计算货架行的累计容量，通过匹配累计值确定每个商品（或商品的拆分部分）对应的货架行。以下是完整实现：

-- 第一步：计算商品的累计库存（包含起始和结束累计值）
;WITH ProductCumulative AS (
    SELECT 
        ProductId,
        Amount,
        SUM(Amount) OVER (ORDER BY ProductId) - Amount AS StartCumulative,
        SUM(Amount) OVER (ORDER BY ProductId) AS EndCumulative
    FROM @ProductTb
),
-- 第二步：计算货架行的累计容量（包含起始和结束累计值）
ShelfRowCumulative AS (
    SELECT 
        Id,
        ShelfId,
        Capacity,
        RowNumber,
        SUM(Capacity) OVER (ORDER BY Id) - Capacity AS StartCapacity,
        SUM(Capacity) OVER (ORDER BY Id) AS EndCapacity
    FROM @ShelfList
)
-- 第三步：关联分配，处理商品完全放入一行或跨多行的情况
SELECT
    pc.ProductId,
    pc.Amount,
    src.ShelfId,
    src.RowNumber,
    src.Capacity AS ShelfRowCapacity,
    -- 计算当前货架行实际存放的商品数量
    CASE
        WHEN pc.StartCumulative >= src.StartCapacity AND pc.EndCumulative <= src.EndCapacity THEN pc.Amount
        WHEN pc.StartCumulative < src.StartCapacity AND pc.EndCumulative <= src.EndCapacity THEN pc.EndCumulative - src.StartCapacity
        WHEN pc.StartCumulative >= src.StartCapacity AND pc.EndCumulative > src.EndCapacity THEN src.EndCapacity - pc.StartCumulative
        ELSE src.EndCapacity - src.StartCapacity
    END AS AllocatedAmount
FROM ProductCumulative pc
JOIN ShelfRowCumulative src
ON pc.EndCumulative > src.StartCapacity AND pc.StartCumulative < src.EndCapacity
ORDER BY pc.ProductId, src.Id;

代码说明 #

1. 商品累计计算：通过窗口函数计算每个商品库存的起始累计值（之前所有商品的总和）和结束累计值（包含当前商品的总和）。
2. 货架行累计计算：同样用窗口函数计算每个货架行的起始累计容量（之前所有行的总容量）和结束累计容量（包含当前行的总容量）。
3. 关联分配：通过累计值的重叠关系，判断商品与货架行的匹配情况，同时处理单个商品跨多个货架行的场景，计算每行实际分配的商品数量。

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