SQL Server/T-SQL : How to update equal percentages of a resultset?

Question

我需要一种方法来获取 KeyID 的结果集并将其尽可能均等地划分，并根据 KeyID 为每个划分更新不同的记录。换句话说，有

SELECT KeyID
FROM   TableA
WHERE (some criteria exists)

我想通过 3 个相等部分的 KeyID 来以 3 种不同的方式更新 TableA。

UPDATE TableA
SET    FieldA = Value1
WHERE  KeyID IN (the first 1/3 of the SELECT resultset above)

UPDATE TableA
SET    FieldA = Value2
WHERE  KeyID IN (the second 1/3 of the SELECT resultset above)

UPDATE TableA
SET    FieldA = Value3
WHERE  KeyID IN (the third 1/3 of the SELECT resultset above)

或者类似的东西。感谢您的所有回复。

Answer 1

With TiledItems As
    (
        Select KeyId
            , NTILE(3) OVER( ORDER BY ... ) As NTileNum
        From TableA
        Where ...
    )
Update TableA
Set FieldA = Case TI.NTileNum
                    When 1 Then Value1
                    When 2 Then Value2
                    When 3 Then Value3
                    End
From TableA As A
    Join TiledItems As TI
        On TI.KeyId = A.KeyId