如何按重量上限48对订单产品分组并拆分大数量条目？

问题背景 #

现有Order表，每条记录包含OrderID（订单ID）、ProductName（产品名称）、Qty（数量）、Weight（总重量）。需求是对每个订单内的产品进行分组，要求每组的总重量不超过48，同时处理单条记录数量过多需要拆分的场景（如Order102的ProductA），最终得到带GroupedID的分组结果。

原始数据 #

预期分组结果 #

实现方案 #

可以通过**递归CTE（公共表表达式）**实现该需求，核心思路是先拆分产品为单个单位，再按重量优先级动态分配分组，最后合并同组记录。以下是SQL Server环境下的实现代码：

-- 1. 拆分产品为单个单位的记录，计算单单位重量
WITH SplitProducts AS (
    SELECT 
        OrderID,
        ProductName,
        1 AS SingleQty,
        Weight / Qty AS SingleWeight
    FROM [Order]
    UNION ALL
    SELECT 
        OrderID,
        ProductName,
        1 AS SingleQty,
        Weight / Qty AS SingleWeight
    FROM SplitProducts sp
    JOIN [Order] o ON sp.OrderID = o.OrderID AND sp.ProductName = o.ProductName
    WHERE (SELECT COUNT(*) FROM SplitProducts sp2 WHERE sp2.OrderID = sp.OrderID AND sp2.ProductName = sp.ProductName) < o.Qty
),
-- 2. 按订单分组，对拆分后的记录按重量降序排序
OrderedProducts AS (
    SELECT 
        OrderID,
        ProductName,
        SingleQty,
        SingleWeight,
        ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY SingleWeight DESC) AS RowNum
    FROM SplitProducts
),
-- 3. 递归分配分组，累计重量超过48则开启新分组
GroupedProducts AS (
    SELECT 
        OrderID,
        ProductName,
        SingleQty,
        SingleWeight,
        RowNum,
        1 AS GroupedID,
        SingleWeight AS CurrentGroupWeight
    FROM OrderedProducts
    WHERE RowNum = 1
    UNION ALL
    SELECT 
        op.OrderID,
        op.ProductName,
        op.SingleQty,
        op.SingleWeight,
        op.RowNum,
        CASE WHEN gp.CurrentGroupWeight + op.SingleWeight > 48 THEN gp.GroupedID + 1 ELSE gp.GroupedID END AS GroupedID,
        CASE WHEN gp.CurrentGroupWeight + op.SingleWeight > 48 THEN op.SingleWeight ELSE gp.CurrentGroupWeight + op.SingleWeight END AS CurrentGroupWeight
    FROM OrderedProducts op
    JOIN GroupedProducts gp ON op.OrderID = gp.OrderID AND op.RowNum = gp.RowNum + 1
),
-- 4. 合并同订单、同产品、同分组的记录
FinalGroups AS (
    SELECT 
        OrderID,
        ProductName,
        SUM(SingleQty) AS Qty,
        SUM(SingleWeight) AS Weight,
        GroupedID
    FROM GroupedProducts
    GROUP BY OrderID, ProductName, GroupedID
)
SELECT * FROM FinalGroups ORDER BY OrderID, GroupedID, ProductName;

代码说明 #

• SplitProducts：递归拆分多数量的产品为单个单位，计算每个单位的重量，为后续分组做准备；
• OrderedProducts：对每个订单内的拆分记录按重量降序排序，优先分配重量大的产品，减少分组数量；
• GroupedProducts：递归累计分组重量，当加入当前单位后总重量超过48时，自动开启新分组；
• FinalGroups：将同分组、同产品的单位记录合并，还原为与预期一致的数量和重量格式。

该方案可完全满足需求，实现订单内产品按总重量不超过48的规则分组，同时处理数量拆分场景。

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