sql-server - 需要一个基于集合的解决方案来对行进行分组

Question

我需要根据 Category 列对一组行进行分组，并根据 SUM(Number) 列将组合行限制为小于或等于@Limit 值。

对于每个不同的Category列，我需要识别<=@limit的“存储桶”。如果 Category 列的所有行的 SUM(Number) 均为 <=@Limit 则该只有 1 个存储桶code>Category 值(如示例数据中的“CCCC”)。但是，如果 SUM(Number)>@limit，则该 Category 值将有多个存储桶行(例如样本数据)，并且每个桶必须是<=@Limit。根据需要可以有任意多个桶。另外，查看 Category 值“DDDD”，它的一行本身就大于 @Limit，并在结果集中分成两行。

鉴于此简化数据:

DECLARE @Detail table (DetailID int  primary key, Category char(4), Number int)
SET NOCOUNT ON
INSERT @Detail VALUES ( 1, 'AAAA',100)
INSERT @Detail VALUES ( 2, 'AAAA', 50)
INSERT @Detail VALUES ( 3, 'AAAA',300)
INSERT @Detail VALUES ( 4, 'AAAA',200)
INSERT @Detail VALUES ( 5, 'BBBB',500)
INSERT @Detail VALUES ( 6, 'CCCC',200)
INSERT @Detail VALUES ( 7, 'CCCC',100)
INSERT @Detail VALUES ( 8, 'CCCC', 50)
INSERT @Detail VALUES ( 9, 'DDDD',800)
INSERT @Detail VALUES (10, 'EEEE',100)
INSERT @Detail VALUES (11, 'AAAA',200) --EDIT added
INSERT @Detail VALUES (12, 'AAAA',200) --EDIT added
INSERT @Detail VALUES (13, 'AAAA',200) --EDIT added
INSERT @Detail VALUES (14, 'AAAA',200) --EDIT added
SET NOCOUNT OFF

DECLARE @Limit int
SET @Limit=500

我需要以下结果集之一:

DetailID  Bucket  |    DetailID  Category Bucket
--------  ------  |    --------  -------- ------
 1        1       |     1        'AAAA'   1     
 2        1       |     2        'AAAA'   1     
 3        1       |     3        'AAAA'   1     
 4        2       |     4        'AAAA'   2     
11        2       |    11        'AAAA'   2      --EDIT added
12        3       |    12        'AAAA'   3      --EDIT added
13        3       |    13        'AAAA'   3      --EDIT added
14        4       |    14        'AAAA'   4      --EDIT added
 5        5       OR    5        'BBBB'   1     
 6        6       |     6        'CCCC'   1     
 7        6       |     7        'CCCC'   1     
 8        6       |     8        'CCCC'   1     
 9        7       |     9        'DDDD'   1     
 9        8       |     9        'DDDD'   2     
10        9       |    10        'EEEE'   1   

编辑尝试完所有答案后

由于对基于集合的解决方案的所有尝试都无法按需要工作，我将对 @GalacticJello Answer 进行修改，修改在下面的代码中注明。我基本上找到了整个类别适合存储桶的所有行，并使用单个 INSERT-SELECT 插入它们，然后使用 @GacticJello 游标自由循环遍历剩余数据。这在我的情况下工作得很好，因为循环几乎不会处理任何行。

DECLARE @DetailTemp table (PID INT IDENTITY(1,1), DetailID int  primary key, Category char(4), Number int) 
DECLARE @DetailFinal table (DetailID int, Category char(4), Bucket int) ---<<<renamed column to Bucket

DECLARE @DetailCount int
SET @DetailCount = 0;

--------<<<optimization added starts here
;WITH AllSingleBuckets AS (
    SELECT
        Category
        FROM @Detail
        GROUP BY Category
        HAVING SUM(Number)<=@Limit

)
INSERT INTO @DetailFinal
        (DetailID, Category, Bucket)
    SELECT
        d.DetailID,d.Category,1
        FROM @Detail                    d
            INNER JOIN AllSingleBuckets s ON d.Category=s.Category
--------<<<optimization added ends here

INSERT @DetailTemp
--------<<<changed for optimization, added WHERE clause
SELECT d.DetailId, d.Category, d.Number FROM @Detail d WHERE NOT EXISTS (SELECT 1 FROM @DetailFinal f WHERE d.Category=f.Category) ORDER BY Category, DetailId
SELECT @DetailCount = @@ROWCOUNT

DECLARE @CurrentPid int
SET @CurrentPid = 1

DECLARE @ThisId int
DECLARE @ThisCategory char(4)
DECLARE @ThisNumber int

DECLARE @CurrentCategory char(4)
DECLARE @CurrentSum INT
DECLARE @CurrentBucket INT


WHILE @CurrentPid <= @DetailCount
BEGIN
    SELECT @ThisId = DetailId, @ThisCategory = Category, @ThisNumber = Number
    FROM @DetailTemp 
    WHERE PID = @CurrentPid

    IF @ThisCategory = @CurrentCategory
    BEGIN
        IF @CurrentSum + @ThisNumber > @Limit
        BEGIN
            SET @CurrentBucket = @CurrentBucket + 1
            SET @CurrentSum = @ThisNumber
        END
        ELSE
        BEGIN
            SET @CurrentSum = @CurrentSum + @ThisNumber
        END
    END
    ELSE
    BEGIN
        SET @CurrentBucket = 1
        SET @CurrentCategory = @ThisCategory
        SET @CurrentSum = @ThisNumber
    END

    WHILE @CurrentSum > @Limit
    BEGIN
        INSERT @DetailFinal SELECT @ThisId, @CurrentCategory, @CurrentBucket
        SET @CurrentBucket = @CurrentBucket + 1
        SET @CurrentSum = @CurrentSum - @Limit
    END

    INSERT @DetailFinal SELECT @ThisId, @CurrentCategory, @CurrentBucket

    SET @CurrentPid = @CurrentPid + 1
END


SELECT * from @DetailFinal ORDER BY Category --------<<<added order by

输出:

DetailID    Category Bucket
----------- -------- -----------
1           AAAA     1
2           AAAA     1
3           AAAA     1
4           AAAA     2
11          AAAA     2
12          AAAA     3
13          AAAA     3
14          AAAA     4
5           BBBB     1
6           CCCC     1
7           CCCC     1
8           CCCC     1
9           DDDD     1
9           DDDD     2
10          EEEE     1

(15 row(s) affected)

Answer 1

DECLARE @Detail table (DetailID int  primary key, Category char(4), Number int) 
SET NOCOUNT ON 
INSERT @Detail VALUES ( 1, 'AAAA',100) 
INSERT @Detail VALUES ( 2, 'AAAA', 50) 
INSERT @Detail VALUES ( 3, 'AAAA',300) 
INSERT @Detail VALUES ( 4, 'AAAA',200) 
INSERT @Detail VALUES ( 5, 'BBBB',500) 
INSERT @Detail VALUES ( 6, 'CCCC',200) 
INSERT @Detail VALUES ( 7, 'CCCC',100) 
INSERT @Detail VALUES ( 8, 'CCCC', 50) 
INSERT @Detail VALUES ( 9, 'DDDD',800) 
INSERT @Detail VALUES (10, 'EEEE',100) 
INSERT @Detail VALUES (11, 'AAAA',200) --EDIT added 
INSERT @Detail VALUES (12, 'AAAA',200) --EDIT added 
INSERT @Detail VALUES (13, 'AAAA',200) --EDIT added 
INSERT @Detail VALUES (14, 'AAAA',200) --EDIT added 
SET NOCOUNT OFF 

DECLARE @Limit int 
SET @Limit=500 

DECLARE @DetailTemp table (PID INT IDENTITY(1,1), DetailID int  primary key, Category char(4), Number int) 
DECLARE @DetailFinal table (DetailID int, Category char(4), Number int) 

DECLARE @DetailCount int
SET @DetailCount = 0;

INSERT @DetailTemp
SELECT DetailId, Category, Number FROM @Detail ORDER BY Category, DetailId
SELECT @DetailCount = @@ROWCOUNT

DECLARE @CurrentPid int
SET @CurrentPid = 1

DECLARE @ThisId int
DECLARE @ThisCategory char(4)
DECLARE @ThisNumber int

DECLARE @CurrentCategory char(4)
DECLARE @CurrentSum INT
DECLARE @CurrentBucket INT


WHILE @CurrentPid <= @DetailCount
BEGIN
    SELECT @ThisId = DetailId, @ThisCategory = Category, @ThisNumber = Number
    FROM @DetailTemp 
    WHERE PID = @CurrentPid

    IF @ThisCategory = @CurrentCategory
    BEGIN
        IF @CurrentSum + @ThisNumber > @Limit
        BEGIN
            SET @CurrentBucket = @CurrentBucket + 1
            SET @CurrentSum = @ThisNumber
        END
        ELSE
        BEGIN
            SET @CurrentSum = @CurrentSum + @ThisNumber
        END
    END
    ELSE
    BEGIN
        SET @CurrentBucket = 1
        SET @CurrentCategory = @ThisCategory
        SET @CurrentSum = @ThisNumber
    END

    WHILE @CurrentSum > @Limit
    BEGIN
        INSERT @DetailFinal SELECT @ThisId, @CurrentCategory, @CurrentBucket
        SET @CurrentBucket = @CurrentBucket + 1
        SET @CurrentSum = @CurrentSum - @Limit
END

    INSERT @DetailFinal SELECT @ThisId, @CurrentCategory, @CurrentBucket

    SET @CurrentPid = @CurrentPid + 1
END


SELECT * from @DetailFinal