如何基于多条件使用CTE更新SQL的ActivityCode列

需求概述 #

现有数据表（截图如下）：

需对ActivityCode列按以下规则更新：

• 当按.拆分后的第三段数字处于1-24区间时，在该段前添加02。例：原110.153.11变为110.153.02.11
• 当按.拆分后的第三段数字处于25-49区间时，在该段前添加01，且最后一段数字从1开始连续计数。例：原110.153.25变为110.153.01.01，110.153.26变为110.153.01.02

完善后的SQL代码 #

WITH cte AS (
    SELECT 
        WorkActivityTypeId,
        ActivityCode,
        -- 提取前两段固定前缀
        LEFT(ActivityCode, CHARINDEX('.', ActivityCode, CHARINDEX('.', ActivityCode) + 1)) AS Prefix,
        -- 提取原第三段数字并转为整数
        CAST(PARSENAME(ActivityCode, 1) AS INT) AS ThirdSegment,
        -- 对25-49区间的行按前两段分组生成连续序号
        ROW_NUMBER() OVER (
            PARTITION BY LEFT(ActivityCode, CHARINDEX('.', ActivityCode, CHARINDEX('.', ActivityCode) + 1))
            ORDER BY CAST(PARSENAME(ActivityCode, 1) AS INT)
        ) AS Seq
    FROM [VanBerloERP_Acc].[dbo].[WorkActivityTypesTest]
    WHERE PARSENAME(ActivityCode, 1) <> '000' -- 排除第三段为000的行
)
UPDATE cte
SET ActivityCode = 
    CASE
        WHEN ThirdSegment BETWEEN 1 AND 24 
            THEN CONCAT(Prefix, '02.', ThirdSegment)
        WHEN ThirdSegment BETWEEN 25 AND 49 
            THEN CONCAT(Prefix, '01.', FORMAT(Seq, '00')) -- 将序号格式化为两位数字
        ELSE ActivityCode -- 其他情况保留原值
    END;

代码说明 #

1. 拆分字符串：用CHARINDEX定位第二个.，提取前两段的固定前缀；借助PARSENAME（从右往左拆分字符串）提取原第三段数字并转为整数，方便后续判断区间。
2. 生成连续序号：针对第三段在25-49的行，按前两段分组，用ROW_NUMBER()生成从1开始的连续序号，满足最后一段从1计数的要求。
3. 分支更新：根据第三段数字的区间，分别拼接对应的前缀和新分段，其他不符合条件的行保持原ActivityCode不变。

内容的提问来源于stack exchange，提问作者Catalin Cernat