SQL Server 2012下按规则为产品分配包装箱的SQL实现问询

核心思路 #

先给按重量排好序的产品编上唯一序号，再根据序号范围匹配对应的包装箱；如果需要考虑包装箱的可用数量，额外统计分配数量并和库存做对比即可。

基础版（不考虑库存限制） #

如果不需要管包装箱的可用数量，直接按规则分配，用下面的SQL就能实现：

WITH RankedProducts AS (
    -- 给产品按重量排序，生成行号
    SELECT 
        ProductID,
        Weight,
        ROW_NUMBER() OVER (ORDER BY Weight) AS RowNum
    FROM Products
)
SELECT 
    ProductID,
    Weight,
    -- 按行号范围分配箱子：前5个Box A，接下来3个Box B，剩余Box C
    CASE
        WHEN RowNum <= 5 THEN 'Box A'
        WHEN RowNum <= 8 THEN 'Box B' -- 前5个之后的3个，对应行号6-8
        ELSE 'Box C'
    END AS AssignedBox
FROM RankedProducts;

进阶版（考虑包装箱可用数量） #

如果要保证分配的产品数量不超过包装箱的库存，需要额外统计已分配数量并和库存对比，完整SQL如下：

WITH RankedProducts AS (
    -- 第一步：给产品按重量排序并生成行号
    SELECT 
        ProductID,
        Weight,
        ROW_NUMBER() OVER (ORDER BY Weight) AS RowNum
    FROM Products
),
BoxAllocations AS (
    -- 第二步：按行号初步匹配对应包装箱
    SELECT 
        rp.ProductID,
        rp.Weight,
        rp.RowNum,
        CASE
            WHEN rp.RowNum <= 5 THEN 'Box A'
            WHEN rp.RowNum <= 8 THEN 'Box B'
            ELSE 'Box C'
        END AS AssignedBox
    FROM RankedProducts rp
),
BoxUsage AS (
    -- 第三步：统计每个包装箱已分配的产品数量
    SELECT 
        AssignedBox,
        COUNT(*) AS AllocatedCount
    FROM BoxAllocations
    GROUP BY AssignedBox
)
-- 第四步：输出符合库存限制的分配结果
SELECT 
    ba.ProductID,
    ba.Weight,
    ba.AssignedBox
FROM BoxAllocations ba
JOIN Boxes b ON ba.AssignedBox = b.BoxName
JOIN BoxUsage bu ON ba.AssignedBox = bu.AssignedBox
WHERE bu.AllocatedCount <= b.AvailableQty
-- 可选：如果某个箱子库存不足，将剩余产品转分配到有库存的Box C
UNION ALL
SELECT 
    rp.ProductID,
    rp.Weight,
    'Box C' AS AssignedBox
FROM RankedProducts rp
LEFT JOIN BoxAllocations ba ON rp.ProductID = ba.ProductID
LEFT JOIN Boxes b ON ba.AssignedBox = b.BoxName
LEFT JOIN BoxUsage bu ON ba.AssignedBox = bu.AssignedBox
WHERE (ba.AssignedBox IS NULL OR bu.AllocatedCount > b.AvailableQty)
AND EXISTS (SELECT 1 FROM Boxes WHERE BoxName = 'Box C' AND AvailableQty > 0);

关键说明 #

• ROW_NUMBER()是SQL Server 2012原生支持的窗口函数，能快速给排序后的产品生成唯一序号，这是实现分配规则的核心。
• 进阶版中的BoxUsage用来统计每个箱子的已分配数量，和包装箱表的AvailableQty对比，避免超库存分配。
• 最后的UNION ALL是可选逻辑，用来处理库存不足的场景——比如Box A只有3个可用，那前3个产品分配Box A，剩下2个原本要分配Box A的产品，会转去有库存的Box C。

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