SQL 运行总计按限制分组

Question

我正在尝试确定如何根据 Qty 列的累计总数将记录分组在一起，以便组大小不超过 50。所需的组在组列中给出，并在下面提供示例数据。

有没有办法在 SQL(特别是 SQL Server 2012)中完成此操作？

感谢您的帮助。

ID      Qty     Group
1       10      1
2       20      1
3       30      2  <- 60 greater than 50 so new group
4       40      3
5       2       3
6       3       3
7       10      4
8       25      4
9       15      4
10      5       5

Answer 1

您可以使用 CTE 来实现目标。

如果其中一件元素超过 50 件，仍然会有一个小组为其分配

DECLARE @Data TABLE (ID int identity(1,1) primary key, Qty int)
INSERT @Data VALUES (10), (20), (30), (40), (2), (3), (10), (25), (15), (5)

;WITH cte AS
(
    SELECT ID, Qty, 1 AS [Group], Qty AS RunningTotal FROM @Data WHERE ID = 1
    UNION ALL
    SELECT data.ID, data.Qty, 
        -- The group limits to 50 Qty
        CASE WHEN cte.RunningTotal + data.Qty > 50 THEN cte.[Group] + 1 ELSE cte.[Group] END, 
        -- Reset the running total for each new group
        data.Qty + CASE WHEN cte.RunningTotal + data.Qty > 50 THEN 0 ELSE cte.RunningTotal END
    FROM @Data data INNER JOIN cte ON data.ID = cte.ID + 1
)
SELECT ID, Qty, [Group] FROM cte