SQL Server 递归查询并合并列数据：CTE与STUFF实现

纸上星河

发布时间：2026-10-15| 2人浏览过

原创

在 SQL Server 中处理层级数据时，常需递归查询某节点的所有父级，并将多行结果拼接为单行字符串。通过 CTE 实现递归遍历，配合 STUFF 和 FOR XML PATH 完成合并，可高效解决此类需求。

核心实现逻辑

要完成从特定节点向上追溯所有上级节点，并将它们的编码或名称合并显示，主要依赖两个关键技术：用于递归查找的 WITH ... AS（CTE）和用于字符串聚合的 STUFF 函数。

第一步：使用 CTE 递归查找所有上级节点

目的：从指定节点出发，逐层向上找到所有祖先节点。

操作：定义一个名为 eps_root 的公用表表达式。首先选择一个初始节点（例如 pk_eps = '1001A11000000003P62E'），然后通过 UNION ALL 将父节点继续与原始表关联，直到没有更上层的父节点为止。

with eps_root(pk_eps, pk_parent, eps_code, eps_name) as
(
    SELECT
         pk_eps, pk_parent, eps_code, eps_name
     FROM pm_eps
     where enablestate = 2
       and pk_eps = '1001A11000000003P62E'
    union all
    SELECT
        e.pk_eps, e.pk_parent, e.eps_code, e.eps_name
    FROM pm_eps e
          inner join eps_root r on e.pk_eps = r.pk_parent
    where e.enablestate = 2
)

第二步：合并列数据并处理排序

目的：将 CTE 产生的多行结果转换为逗号或斜杠分隔的单行字符串，并确保顺序正确。

操作：在外层查询中，使用 STUFF 函数配合子查询来拼接字符串。

合并编码：通过 FOR XML PATH('') 将所有不重复的 eps_code 加上前缀 '/' 拼接起来，STUFF 去除第一个多余的 '/'。
合并名称与排序：为了确保中文名称不乱序，需要在子查询中对 eps_name 按照 eps_code 进行排序。使用 TOP 99.99 PERCENT 是为了让 ORDER BY 在存在 TOP 时生效（SQL Server 的一种技巧）。最后同样用 STUFF 去除首字符。

select distinct 
	eps_code = stuff((select '/' + convert(nvarchar(500), eps_code) from (select distinct eps_code from eps_root) r1 for xml path('')) , 1, 1, ''),
	--此次需要使用eps_code排序，不然中文的会错乱顺序
	eps_name = stuff((select '/' + eps_name from (select top 99.99 PERCENT eps_name from eps_root order by eps_code) r2 for xml path('')), 1, 1, '')
from eps_root