如何在FOR XML类型的字符串拼接查询中添加DISTINCT

我有一个用FOR XML做字符串拼接的查询，运行正常但会返回重复值。要是普通独立列查询，加DISTINCT再把SELECT里的每个项都放进ORDER BY就能解决去重，但在这个拼接场景里加DISTINCT会触发SQL Server报错，提示ORDER BY中的每个项都必须包含在SELECT列表中，可要是把排序字段加到拼接的SELECT里，又会破坏最终的字符串格式。后续SQLCLR代码必须依赖单列的拼接数据，所以得在保留格式的前提下调整查询实现去重。

原查询代码：

coalesce(stuff((select '; ' + convert(nvarchar, t0.docnum) + ': '
                    + convert(nvarchar, t0.duedate, 101) + ', '
                    + convert(nvarchar, convert(int, t0.plannedqty))
                from owor t0
                inner join wor1 t1 on t0.docentry = t1.docentry
                where t0.status in ('P','R')
                  and t1.warehouse in (select value from string_split(@Warehouses,','))
                  and t0.itemcode = s.itemcode
                order by t0.duedate
                for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, N''), N'') as p_pros_data,

核心思路是先对要拼接的基础数据去重，再执行字符串拼接，把去重逻辑放到内层子查询或CTE中，避开直接在拼接的SELECT里加DISTINCT导致的排序冲突。

方法一：内层子查询先去重 #

先从关联表中筛选出不重复的记录（按拼接用到的docnum、duedate、plannedqty字段去重），再基于去重后的结果做拼接：

coalesce(stuff((select '; ' + convert(nvarchar, dist.docnum) + ': '
                    + convert(nvarchar, dist.duedate, 101) + ', '
                    + convert(nvarchar, convert(int, dist.plannedqty))
                from (
                    -- 先提取并去重所需字段
                    select distinct t0.docnum, t0.duedate, t0.plannedqty
                    from owor t0
                    inner join wor1 t1 on t0.docentry = t1.docentry
                    where t0.status in ('P','R')
                      and t1.warehouse in (select value from string_split(@Warehouses,','))
                      and t0.itemcode = s.itemcode
                ) as dist
                order by dist.duedate
                for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, N''), N'') as p_pros_data,

方法二：用CTE预处理去重数据 #

用CTE先整理出去重后的数据集，再基于CTE做拼接，逻辑更清晰，适合嵌入到复杂的外层查询中：

-- 嵌入到原大查询中使用
with DistinctWorRecords as (
    select distinct t0.docnum, t0.duedate, t0.plannedqty
    from owor t0
    inner join wor1 t1 on t0.docentry = t1.docentry
    where t0.status in ('P','R')
      and t1.warehouse in (select value from string_split(@Warehouses,','))
      and t0.itemcode = s.itemcode
)
select 
    -- 原查询的其他字段...
    coalesce(stuff((select '; ' + convert(nvarchar, d.docnum) + ': '
                        + convert(nvarchar, d.duedate, 101) + ', '
                        + convert(nvarchar, convert(int, d.plannedqty))
                    from DistinctWorRecords d
                    order by d.duedate
                    for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, N''), N'') as p_pros_data
    -- 原查询的其他字段...
from ... -- 原外层查询的表和过滤条件

注意事项 #

• 去重字段可按需调整：如果docnum本身是唯一标识，也可以只按docnum去重，避免误删合法的不同记录。
• 内层去重后的数据集里，排序字段duedate已包含在SELECT列表中，完全符合SQL Server语法要求，不会触发报错。
• 最终拼接字符串格式和原查询完全一致，满足SQLCLR代码的需求。

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