SQL Server按lotno分组更新TrackingNumber为最大值逐次加1问题

要实现同LotNO分组统一赋值、不同组按全局最大值依次递增的需求，可以使用SQL Server支持的可更新CTE（公用表表达式）实现，具体语句如下：

WITH GlobalMax AS (
    -- 先获取表中当前TrackingNumber的全局最大值
    SELECT MAX(TrackingNumber) AS MaxTrack FROM TEST.DBO.J_CordToolARCCutSheetPlanning
),
LotGroup AS (
    -- 给需要更新的LotNO分配递增序号，同LotNO序号一致
    SELECT 
        j.LotNO,
        -- DENSE_RANK保证同组序号相同，不同组连续递增
        gm.MaxTrack + DENSE_RANK() OVER(ORDER BY j.LotNO) AS NewTrack
    FROM TEST.DBO.J_CordToolARCCutSheetPlanning j
    CROSS JOIN GlobalMax gm
    WHERE j.JobNumber = '1234' AND j.TrackingNumber = 0
    GROUP BY j.LotNO, gm.MaxTrack
)
UPDATE t
SET t.TrackingNumber = lg.NewTrack
FROM TEST.DBO.J_CordToolARCCutSheetPlanning t
INNER JOIN LotGroup lg ON t.LotNO = lg.LotNO
WHERE t.JobNumber = '1234' AND t.TrackingNumber = 0

语句逻辑说明 #

• 第一层CTEGlobalMax查询获得当前表TrackingNumber的全局最大值，示例中该值为5
• 第二层CTELotGroup筛选出作业1234中TrackingNumber为0的记录，按LotNO分组后用DENSE_RANK()窗口函数给每个不同的LotNO分配连续的递增序号，同LotNO序号一致，再叠加全局最大值得到每组要更新的目标值
• 最后通过关联更新将目标值写入原表，保证同LotNO的记录TrackingNumber取值一致，不同组依次递增
  示例数据执行上述语句后，即可得到期望的更新结果。

内容的提问来源于stack exchange，提问作者PeteyN.O