基于同ID行值修改条件的SQL查询实现需求

正确实现代码 #

WITH id_mapping AS (
    SELECT 
        ID,
        SUBSTRING(Column2, 2, 1) AS num,
        CASE 
            WHEN Column2 LIKE 'F%AGE' AND Column6 = 2 THEN 'F'
            WHEN Column2 LIKE 'M%AGE' AND Column6 = 1 THEN 'M'
        END AS replace_char
    FROM #results
    WHERE (Column2 LIKE 'F[1-9]AGE' AND Column6 = 2) 
       OR (Column2 LIKE 'M[1-9]AGE' AND Column6 = 1)
)
UPDATE r
SET Column2 = REPLACE(r.Column2, 'NB', im.replace_char)
FROM #results r
JOIN id_mapping im 
    ON r.ID = im.ID 
    AND SUBSTRING(r.Column2, 3, 1) = im.num
WHERE r.Column2 LIKE 'NB[1-9]WORK' 
   OR r.Column2 LIKE 'NB[1-9]HOH';

逻辑说明 #

1. 预筛选匹配规则的AGE行：通过CTE id_mapping提取所有符合规则的F[x]AGE（Column6=2）和M[x]AGE（Column6=1）行，记录对应的ID、数字标识x以及要替换的字符（F/M）。
2. 关联更新NB行：将原表中所有NB[x]WORK/NB[x]HOH的行，与CTE中同ID、同数字标识x的记录关联，把NB替换为对应的F或M。
3. 处理多规则共存场景：像ID26这种同时存在M1AGE和F2AGE的情况，会根据NB行的数字标识分别匹配对应的替换规则，生成符合预期的结果。

原有代码问题分析 #

• 初始更新语句的筛选条件错误，误匹配NB[1-9]AGE行，且未关联同一ID下的目标NB行，完全不符合规则逻辑。
• 第二个更新语句的逻辑存在优先级问题（OR优先级低于AND），且未匹配数字标识，无法处理同一ID下同时存在M/F开头AGE行的场景。

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