SQL Server 2012 如何按箱子数量为排序后的产品分配标签?
可以用 ROW_NUMBER() 给产品编号,再用累计 SUM() 将箱子数量转换为编号区间,通过区间连接分配标签。该方案适用于 SQL Server 2012。
按题意,Box A 和 Box B 按各自的数量分配,Box C 接收全部剩余产品,因此不设置数量上限。如果 Box C 也必须遵守库存上限,需要调整最后一个箱子的匹配条件。
将数量转换成编号区间
按重量给产品编号后,分配规则可以表示为:
| 箱子 | 数量规则 | 对应产品编号 |
|---|---|---|
| Box A | 5 个 | 1~5 |
| Box B | 3 个 | 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,可以据此识别尚未分配的产品。
备注:内容仅供参考。
