如何基于#test1表替换#finaloutput的ParamValue值？SQL Server求助

现有数据 #

映射表（#test1） #

执行查询：

select * from #test1

返回结果：

待处理数据（#finaloutput筛选结果） #

执行查询：

select *
from #finaloutput
where EquipId like '%00%'
and ParamName like 'MVE';

返回结果：

需求 #

将ParamValue中的昵称（如C、D、J）替换为对应的equip_id，得到如下结果：

尝试的错误代码 #

你使用的游标代码存在两个核心问题：一是替换方向搞反了（把equip_id替换成child_nickname，实际需要反过来）；二是查询的表是#test而非实际的#test1，还包含了空行数据。错误代码如下：

DECLARE replace_cursor CURSOR FOR 
SELECT equip_id, child_nickname 
FROM #test

OPEN replace_cursor

FETCH NEXT FROM replace_cursor 
INTO @equip_id, @child_nickname

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE #finaloutput
    SET ParamValue = REPLACE(ParamValue, @equip_id, @child_nickname)
    WHERE ParamValue LIKE '%' + @equip_id + '%'

    FETCH NEXT FROM replace_cursor 
    INTO @equip_id, @child_nickname
END

CLOSE replace_cursor
DEALLOCATE replace_cursor

解决方案 #

方法1：修正后的游标 #

调整替换方向，修正表名并过滤空值：

DECLARE @equip_id VARCHAR(10), @child_nickname VARCHAR(10)

DECLARE replace_cursor CURSOR FOR 
SELECT equip_id, child_nickname 
FROM #test1
WHERE equip_id IS NOT NULL AND child_nickname IS NOT NULL -- 过滤空行

OPEN replace_cursor

FETCH NEXT FROM replace_cursor 
INTO @equip_id, @child_nickname

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE #finaloutput
    SET ParamValue = REPLACE(ParamValue, @child_nickname, @equip_id) -- 替换方向反转
    WHERE ParamName = 'MVE' AND EquipId LIKE '%00%' -- 缩小更新范围提升效率

    FETCH NEXT FROM replace_cursor 
    INTO @equip_id, @child_nickname
END

CLOSE replace_cursor
DEALLOCATE replace_cursor

方法2：高效的字符串拆分重组（推荐） #

对于SQL Server 2017及以上版本，使用STRING_SPLIT和STRING_AGG实现无游标替换，效率更高：

WITH SplitValues AS (
    SELECT 
        t.Equip,
        t.Recipe,
        t.ParamName,
        -- 拆分ParamValue，同时保留括号信息
        CASE 
            WHEN v.value LIKE '(%' THEN SUBSTRING(v.value, 2, LEN(v.value)-2)
            ELSE v.value
        END AS Nickname,
        CASE 
            WHEN v.value LIKE '(%' THEN '()' -- 标记是否带括号
            ELSE ''
        END AS Brackets
    FROM #finaloutput t
    CROSS APPLY STRING_SPLIT(t.ParamValue, '&') v
    WHERE t.ParamName = 'MVE' AND t.EquipId LIKE '%00%'
),
MappedValues AS (
    SELECT 
        s.Equip,
        s.Recipe,
        s.ParamName,
        -- 根据标记补回括号
        CASE 
            WHEN s.Brackets = '()' THEN '(' + COALESCE(m.equip_id, s.Nickname) + ')'
            ELSE COALESCE(m.equip_id, s.Nickname)
        END AS MappedValue
    FROM SplitValues s
    LEFT JOIN #test1 m ON s.Nickname = m.child_nickname
)
SELECT 
    Equip,
    Recipe,
    ParamName,
    STRING_AGG(MappedValue, '&') AS ParamValue
FROM MappedValues
GROUP BY Equip, Recipe, ParamName;

如果需要直接更新#finaloutput表，可以用CTE结合更新：

WITH SplitValues AS (
    SELECT 
        t.Equip,
        t.Recipe,
        t.ParamName,
        t.ParamValue AS OriginalValue,
        -- 拆分ParamValue，同时保留括号信息
        CASE 
            WHEN v.value LIKE '(%' THEN SUBSTRING(v.value, 2, LEN(v.value)-2)
            ELSE v.value
        END AS Nickname,
        CASE 
            WHEN v.value LIKE '(%' THEN '()'
            ELSE ''
        END AS Brackets,
        v.value AS OriginalPart
    FROM #finaloutput t
    CROSS APPLY STRING_SPLIT(t.ParamValue, '&') v
    WHERE t.ParamName = 'MVE' AND t.EquipId LIKE '%00%'
),
MappedValues AS (
    SELECT 
        s.Equip,
        s.Recipe,
        s.ParamName,
        s.OriginalValue,
        CASE 
            WHEN s.Brackets = '()' THEN '(' + COALESCE(m.equip_id, s.Nickname) + ')'
            ELSE COALESCE(m.equip_id, s.Nickname)
        END AS MappedValue
    FROM SplitValues s
    LEFT JOIN #test1 m ON s.Nickname = m.child_nickname
),
Recombined AS (
    SELECT 
        Equip,
        Recipe,
        ParamName,
        OriginalValue,
        STRING_AGG(MappedValue, '&') AS NewParamValue
    FROM MappedValues
    GROUP BY Equip, Recipe, ParamName, OriginalValue
)
UPDATE f
SET f.ParamValue = r.NewParamValue
FROM #finaloutput f
JOIN Recombined r ON f.Equip = r.Equip 
    AND f.Recipe = r.Recipe 
    AND f.ParamName = r.ParamName 
    AND f.ParamValue = r.OriginalValue;

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