sql - SQL Server 2008：将表行分组在一起

Question

我有一个 SQL Server 2008 表，我需要将行分组为部分或组。每个部分都以部件号“组”开头。我想用唯一标识每个组的代码更新一个字段。我创建了一个光标来完成此操作，但我想要更快的东西。批处理操作会很棒，并且可以接受更快的游标。谢谢

USE QuoteWerks

DECLARE @DocNo VARCHAR(30) = 'JHEAQ5947'
DECLARE @validation_code nvarchar(30) = ''
DECLARE @group_code int = 0
DECLARE @part_number nvarchar(30)

DECLARE myCursor CURSOR FORWARD_ONLY FOR
    SELECT DI.ManufacturerPartNumber
    FROM DocumentHeaders AS DH
    INNER JOIN DocumentItems AS DI ON DH.ID = DI.DocID
    WHERE DH.DocNo = @DocNo
    ORDER BY DI.ID
    FOR UPDATE OF DI.CustomText14, DI.CustomText03

OPEN myCursor

FETCH NEXT FROM myCursor INTO @part_number

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @part_number = 'Group'
    BEGIN
        SELECT @group_code = @group_code + 1
       
        UPDATE DI
        SET DI.CustomText14 = @group_code, DI.CustomText03 = @validation_code
        FROM DocumentHeaders AS DH
        INNER JOIN DocumentItems AS DI ON DH.ID = DI.DocID
        WHERE CURRENT OF myCursor
    END
    ELSE
    BEGIN
        UPDATE DI
        SET DI.CustomText14 = @group_code
        FROM DocumentHeaders AS DH
        INNER JOIN DocumentItems AS DI ON DH.ID = DI.DocID
        WHERE CURRENT OF myCursor
    END  

    FETCH NEXT FROM myCursor INTO @part_number
END        

CLOSE myCursor
DEALLOCATE myCursor

标签： sqlsql-server-2008