如何在SQL Server中提取两字符串聚合列的相同值？

你的原始代码存在逻辑问题：它计算的是全表所有Column_A的代码与所有Column_B的代码的全局交集，而非每行自身Column_A和Column_B的交集，这不符合需求。以下是针对百万级数据、长列表场景优化的高效方案：

方案一：拆分后内连接求交集（适合代码无重复场景） #

针对每行拆分两列后做内连接，直接筛选出同时存在的代码，再聚合结果：

SELECT
    t.Column_A,
    t.Column_B,
    STRING_AGG(a.value, ';') AS Column_C
FROM MyTable t
CROSS APPLY STRING_SPLIT(t.Column_A, ';') a
JOIN STRING_SPLIT(t.Column_B, ';') b ON a.value = b.value
GROUP BY t.Column_A, t.Column_B

如果同一列内存在重复代码，需添加DISTINCT去重：

SELECT
    t.Column_A,
    t.Column_B,
    STRING_AGG(DISTINCT a.value, ';') AS Column_C
FROM MyTable t
CROSS APPLY STRING_SPLIT(t.Column_A, ';') a
JOIN STRING_SPLIT(t.Column_B, ';') b ON a.value = b.value
GROUP BY t.Column_A, t.Column_B

方案二：避免拆分B列，利用字符串匹配（性能更优） #

如果代码本身不包含分号（符合原数据的分隔规则），可以给Column_B前后拼接分号，通过CHARINDEX直接查找拆分后的Column_A代码是否存在，避免拆分Column_B列，大幅减少计算开销：

SELECT
    t.Column_A,
    t.Column_B,
    STRING_AGG(a.value, ';') AS Column_C
FROM MyTable t
CROSS APPLY STRING_SPLIT(t.Column_A, ';') a
WHERE CHARINDEX(';' + a.value + ';', ';' + t.Column_B + ';') > 0
GROUP BY t.Column_A, t.Column_B

需去重时同样添加DISTINCT：

SELECT
    t.Column_A,
    t.Column_B,
    STRING_AGG(DISTINCT a.value, ';') AS Column_C
FROM MyTable t
CROSS APPLY STRING_SPLIT(t.Column_A, ';') a
WHERE CHARINDEX(';' + a.value + ';', ';' + t.Column_B + ';') > 0
GROUP BY t.Column_A, t.Column_B

额外性能优化建议 #

• 若代码长度固定或较短，CHARINDEX的查找效率会更高；
• 若需频繁执行此类查询，可考虑将Column_A和Column_B预先拆分成关联表（存储行ID与对应代码），避免重复拆分操作；
• 对于SQL Server 2022+版本，可使用STRING_SPLIT(..., ';', 1)启用序号参数，但因需求不要求顺序，对性能影响有限；
• 可给拆分后的代码列创建统计信息，帮助优化器生成更优执行计划。

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