PAYATHON 2026

SQL Server 2012 如何按箱子数量为排序后的产品分配标签?

数据小许

可以用 ROW_NUMBER() 给产品编号,再用累计 SUM() 将箱子数量转换为编号区间,通过区间连接分配标签。该方案适用于 SQL Server 2012。

按题意,Box A 和 Box B 按各自的数量分配,Box C 接收全部剩余产品,因此不设置数量上限。如果 Box C 也必须遵守库存上限,需要调整最后一个箱子的匹配条件。

将数量转换成编号区间

按重量给产品编号后,分配规则可以表示为:

箱子数量规则对应产品编号
Box A5 个1~5
Box B3 个6~8
Box C全部剩余产品9 及以后

SQL 查询只需判断产品编号落在哪个区间,无须逐个循环。

使用 CTE 和区间连接

原问题没有提供表结构,下面使用示例表名和字段名,执行时请替换为实际名称:

  • dbo.Products:ProductID 为唯一产品编号,Weight 为重量。
  • dbo.Boxes:BoxName 为箱子名称,Qty 为可分配数量,BoxOrder 为明确的分配顺序。

假设箱子表中依次配置 Box A、Box B、Box C,前两个箱子的 Qty 分别为 5、3。BoxOrder 必须唯一,Qty 必须是非空、非负整数;最后一个箱子的 Qty 不作为分配上限。

;WITH RankedProducts AS
(
    SELECT
        ProductID,
        Weight,
        ROW_NUMBER() OVER (
            ORDER BY Weight ASC, ProductID ASC
        ) AS ProductNo
    FROM dbo.Products
),
BoxTotals AS
(
    SELECT
        BoxName,
        BoxOrder,
        CAST(Qty AS bigint) AS Qty,
        SUM(CAST(Qty AS bigint)) OVER (
            ORDER BY BoxOrder
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS EndNo,
        ROW_NUMBER() OVER (
            ORDER BY BoxOrder DESC
        ) AS ReverseOrder
    FROM dbo.Boxes
),
BoxRanges AS
(
    SELECT
        BoxName,
        EndNo - Qty AS StartNo,
        EndNo,
        ReverseOrder
    FROM BoxTotals
)
SELECT
    p.ProductID,
    p.Weight,
    b.BoxName
FROM RankedProducts AS p
LEFT JOIN BoxRanges AS b
    ON p.ProductNo > b.StartNo
   AND (
        p.ProductNo <= b.EndNo
        OR b.ReverseOrder = 1
   )
ORDER BY p.ProductNo;

StartNo 是区间的下界,匹配时不包含这个值。例如,Box B 的累计数量为 8,自身数量为 3,下界就是 8 - 3 = 5,匹配编号大于 5 且小于等于 8 的产品。

ReverseOrder = 1 标识分配顺序中的最后一个箱子。它只检查编号是否超过下界,并接收全部剩余产品。在上述配置中,这个箱子是 Box C。

排序和数量边界

示例假设产品按重量升序排列。如果需要从重到轻分配,将 Weight ASC 改为 Weight DESC。重量相同时,用唯一的 ProductID 确定先后顺序,避免标签分配不稳定。

箱子的顺序由 BoxOrder 决定,应明确配置为 Box A、Box B、Box C,不能依赖表的存储顺序。最后一个箱子必须存在;如果箱子表为空,LEFT JOIN 会保留产品,但 BoxName 为 NULL。

如果所有箱子都要受数量限制,包括 Box C,将连接条件改为:

ON p.ProductNo > b.StartNo
AND p.ProductNo <= b.EndNo

此时,超过全部箱子累计容量的产品仍会保留在结果中,其 BoxName 为 NULL,可以据此识别尚未分配的产品。

备注:内容仅供参考。