SQL实现拣货量按箱分配：剩余量分配至最后一行的需求及问题

我来帮你搞定这个产品数量分配的问题！你的需求很明确：两款产品（CEOO1、CEOO2）各50件，每箱装15件，需要把总数量拆分成多行，前N行各分配15件，最后一行分配剩余的不足一箱的数量。你的现有代码在计算剩余量、行排序和分配逻辑上有几个问题，我来给你修正并提供正确的实现。

现有代码的问题分析 #

• 排序逻辑不可靠：你用ORDER BY ProductCode做窗口函数的排序，但同一产品的所有行ProductCode都相同，无法保证行的顺序，导致累计求和的结果混乱。
• 剩余量计算错误：Quantity - SUM(Size) OVER (...)的逻辑会导致最后一行出现负数（比如50-60=-10），这显然不符合需求。
• 未正确计算分配量：你把Allocated固定设为0，完全没实现分配逻辑。

正确的SQL实现方案 #

下面的代码会先为每个产品生成对应的拆分行数，然后根据行号计算每一行的分配数量，最后一行自动分配剩余量：

DECLARE @TempTable TABLE (
    ID INT IDENTITY (1,1) PRIMARY KEY,
    ProductCode NVARCHAR(50) NULL,
    Quantity INT NULL,
    Size INT NULL
)
INSERT INTO @TempTable VALUES('CEOO1',50,15)
INSERT INTO @TempTable VALUES('CEOO2',50,15)

SELECT
    ProductCode,
    Quantity,
    Size,
    -- 计算剩余数量：总数量减去已分配的总和
    Quantity - SUM(Allocated) OVER (PARTITION BY ProductCode ORDER BY RowNum ROWS UNBOUNDED PRECEDING) AS Remaining,
    Allocated
FROM (
    SELECT
        detail.ProductCode,
        detail.Quantity,
        detail.Size,
        -- 为每个产品的行生成序号
        ROW_NUMBER() OVER (PARTITION BY detail.ProductCode ORDER BY (SELECT NULL)) AS RowNum,
        -- 计算总行数
        CEILING(CAST(detail.Quantity AS FLOAT)/CAST(detail.Size AS FLOAT)) AS TotalRows,
        -- 分配逻辑：非最后一行分配Size，最后一行分配剩余量
        CASE
            WHEN ROW_NUMBER() OVER (PARTITION BY detail.ProductCode ORDER BY (SELECT NULL)) < CEILING(CAST(detail.Quantity AS FLOAT)/CAST(detail.Size AS FLOAT))
            THEN detail.Size
            ELSE detail.Quantity - (CEILING(CAST(detail.Quantity AS FLOAT)/CAST(detail.Size AS FLOAT)) - 1)*detail.Size
        END AS Allocated
    FROM @TempTable detail
    CROSS APPLY (
        -- 生成对应行数的记录
        SELECT number FROM master.dbo.spt_values 
        WHERE type = 'P' AND number > 0 AND number <= CEILING(CAST(detail.Quantity AS FLOAT)/CAST(detail.Size AS FLOAT))
    ) RowGen
) AS ProductAllocation
ORDER BY ProductCode, RowNum

代码关键点说明 #

1. 可靠的行编号：用ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY (SELECT NULL))为每个产品的拆分行生成唯一序号，保证行顺序稳定。
2. 正确的分配逻辑：通过判断当前行号是否小于总行数，决定分配整箱量（15）还是剩余量（比如50-3*15=5）。
3. 准确的剩余量计算：用窗口函数累计已分配的数量，再用总数量减去累计值得到剩余量，确保每一行的剩余量都是正确的。

预期执行结果 #

这样就完全符合你的需求了：每个产品前3行各分配15，最后一行分配剩余的5件。

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