SQL Synapse 两表多值分隔列对比合并后更新表A实现方案咨询

实现逻辑 #

• 分别拆分表A、表B的TEL多值字符串为单值行
• 合并同ID下的所有手机号后去重
• 将去重后的单值重新拼接为逗号分隔字符串
• 关联更新到表A对应ID的TEL字段

可运行SQL代码 #

WITH 
-- 拆分表A的TEL为单值
a_split AS (
    SELECT 
        id,
        value AS tel
    FROM A
    CROSS APPLY STRING_SPLIT(tel, ',')
),
-- 拆分表B的TEL为单值
b_split AS (
    SELECT 
        id,
        value AS tel
    FROM B
    CROSS APPLY STRING_SPLIT(tel, ',')
),
-- 合并两表拆分后的数据并自动去重
union_tel AS (
    SELECT id, tel FROM a_split
    UNION
    SELECT id, tel FROM b_split
),
-- 按ID分组拼接回逗号分隔的字符串，可自定义排序规则
merged_tel AS (
    SELECT 
        id,
        STRING_AGG(tel, ',') WITHIN GROUP (ORDER BY tel) AS new_tel
    FROM union_tel
    GROUP BY id
)
-- 更新表A的TEL字段
UPDATE A
SET A.tel = M.new_tel
FROM A
INNER JOIN merged_tel M ON A.id = M.id

注意事项 #

• UNION会自动对两表的合并结果去重，不需要额外加DISTINCT
• 如果需要自定义拼接后的手机号顺序，可以修改WITHIN GROUP (ORDER BY xxx)部分的排序规则
• 如果存在部分ID只在B表存在、需要同步新增到A表的场景，可以将最后一步的UPDATE替换为MERGE语法实现增改一体化
• 原有代码的问题在于仅拆分了B表的TEL、未拆分A表的多值TEL，用LIKE匹配多值整串无法实现单值的比对逻辑

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