SQL中STUFF函数拼接去重列值的排序异常问题咨询

你的问题根源在于每个STUFF子查询中的DISTINCT是独立执行的，数据库会对每个子查询的结果单独排序（通常按被拼接的字段排序），导致不同列的拼接结果顺序无法对应。比如Agreement_Cds按Agreement_Cd排序，而Agreement_id_Qtys按拼接后的Agreement_ID_Agreement_Qty字符串排序，两者的顺序自然无法匹配。

修复方案：统一去重 + 一致排序 #

我们需要先对原表数据按分组维度+所有需拼接字段做去重，然后在所有拼接子查询中使用同一个排序字段，确保所有列的拼接顺序完全对应。

以下是修改后的SQL代码：

-- 先创建去重的CTE，保留所有需要关联的字段
WITH DistinctTableA AS (
    SELECT DISTINCT
        AcquireNbr,
        Working_Day,
        Working_Cd,
        LTRIM(RTRIM(Agreement_Cd)) AS Agreement_Cd,
        CONVERT(varchar(10), Agreement_ID) + '_' + CONVERT(varchar(15), Agreement_Qty) AS Agreement_id_Qty,
        LTRIM(RTRIM(Agreement_Receiver_Cd)) AS Agreement_Receiver_Cd
    FROM #TableA
)

-- 基于去重后的CTE进行分组拼接，所有子查询使用相同排序字段
SELECT
    AcquireNbr,
    Working_Day,
    Working_Cd,
    Agreement_Cds = STUFF((
        SELECT ', ' + Agreement_Cd
        FROM DistinctTableA b
        WHERE b.AcquireNbr = a.AcquireNbr
          AND b.Working_Day = a.Working_Day
          AND b.Working_Cd = a.Working_Cd
        ORDER BY b.Agreement_Cd  -- 统一排序字段，保证所有列顺序一致
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(60)'), 1, 2, ''),
    Agreement_id_Qtys = STUFF((
        SELECT ', ' + Agreement_id_Qty
        FROM DistinctTableA b
        WHERE b.AcquireNbr = a.AcquireNbr
          AND b.Working_Day = a.Working_Day
          AND b.Working_Cd = a.Working_Cd
        ORDER BY b.Agreement_Cd  -- 和上面保持相同的排序逻辑
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(80)'), 1, 2, ''),
    Agreement_Receiver_Cds = STUFF((
        SELECT ', ' + Agreement_Receiver_Cd
        FROM DistinctTableA b
        WHERE b.AcquireNbr = a.AcquireNbr
          AND b.Working_Day = a.Working_Day
          AND b.Working_Cd = a.Working_Cd
        ORDER BY b.Agreement_Cd  -- 统一排序
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(80)'), 1, 2, '')
FROM DistinctTableA a
GROUP BY AcquireNbr, Working_Day, Working_Cd;

额外优化点 #

1. 避免特殊字符转义：使用FOR XML PATH(''), TYPE后再通过.value('.', ...)提取字符串，能防止原始数据中的&、<、>等特殊字符被转义成XML实体（比如&），让拼接结果更准确。
2. 统一去重逻辑：将去重逻辑放在CTE中，避免重复执行DISTINCT，提升查询效率。

你可以根据实际需求调整排序字段（比如改成Agreement_ID或者其他业务相关字段），只要所有拼接子查询的ORDER BY保持一致，就能保证各列的拼接顺序完全匹配。

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