使用STUFF函数按父ID分组获取子节点值的问题排查

问题背景 #

需要将自引用表中的子节点Value合并到单个单元格，尝试使用STUFF函数但未得到预期结果，同时使用CONCAT和CONCAT_WS函数时出现报错：not a recognized built-in function name。

表结构 #

当前查询语句 #

SELECT TaskParent.TaskID,
    ValueValues = STUFF((
            SELECT DISTINCT ' | ' + TaskChildren.Value
            FROM tblTask TaskChildren
                JOIN tblTask TaskParent2 ON TaskParent.TaskID = TaskChildren.TaskGroupID
            WHERE
                TaskParent2.TaskID = TaskParent.TaskID
                AND TaskParent2.TaskID not like TaskParent2.TaskParentID
            FOR XML PATH('')
            ), 1, 3, ''
        )
FROM tblTask Task
    JOIN tblTask TaskParent ON TaskParent.TaskID = Task.ParentID
GROUP BY TaskParent.TaskID

当前查询结果 #

期望结果 #

添加Task.ID后的查询结果 #

修改后的查询语句 #

select TaskParents.ID, substring((select ' | ' + Value from tblTask TaskValues where TaskValues.ParentID = TaskParents.ID for xml path('')), 4, 99999) as Children
from tblTask Task
    LEFT JOIN tblTask TaskParents ON TaskParents.ID = Task.ParentID
group by TaskParents.ID
ORDER BY TaskParents.ID

问题分析与正确解决方案 #

原查询的问题 #

1. 关联字段错误：原查询中使用TaskParent.TaskID = TaskChildren.TaskGroupID，但表结构中不存在TaskGroupID字段，正确关联应该是TaskChildren.ParentID = TaskParent.ID。
2. 条件逻辑冗余：TaskParent2.TaskID not like TaskParent2.TaskParentID使用like不合适，且逻辑混乱，应该直接筛选子节点（即TaskChildren.ID != TaskChildren.ParentID，排除自引用的父节点）。
3. 分组逻辑不合理：原查询通过关联子节点再分组的方式，容易导致结果遗漏。

正确的查询语句 #

SELECT 
    Parent.ID AS ParentID,
    STUFF((
        SELECT ' / ' + Child.Value
        FROM tblTask Child
        WHERE Child.ParentID = Parent.ID
          AND Child.ID != Child.ParentID -- 排除自引用的节点
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 3, '') AS Value
FROM tblTask Parent
WHERE EXISTS (
    SELECT 1 
    FROM tblTask Child 
    WHERE Child.ParentID = Parent.ID 
      AND Child.ID != Child.ParentID
)

说明 #

• 直接从父节点出发，通过Child.ParentID = Parent.ID精准筛选对应子节点，同时排除自引用的节点（比如ID=1的自身节点）。
• 使用STUFF函数移除开头的分隔符/，相比SUBSTRING更灵活，不会因分隔符长度变化出错。
• 加入TYPE和.value('.', 'NVARCHAR(MAX)')可以避免XML转义字符（如&、<等）被转义的问题。
• EXISTS条件确保只返回存在子节点的父节点，避免出现空值结果。

CONCAT/CONCAT_WS报错原因 #

CONCAT和CONCAT_WS是SQL Server 2012及以上版本才支持的函数，如果你的SQL Server版本低于2012，就会出现该报错，此时只能使用+运算符进行字符串拼接。

对修改后查询的优化建议 #

你修改后的查询可以调整为以下形式，解决自引用节点的问题并提升灵活性：

SELECT 
    TaskParents.ID AS ParentID,
    STUFF((
        SELECT ' | ' + TaskValues.Value
        FROM tblTask TaskValues 
        WHERE TaskValues.ParentID = TaskParents.ID
          AND TaskValues.ID != TaskValues.ParentID
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 3, '') AS Children
FROM tblTask TaskParents
WHERE EXISTS (
    SELECT 1 
    FROM tblTask TaskValues 
    WHERE TaskValues.ParentID = TaskParents.ID 
      AND TaskValues.ID != TaskValues.ParentID
)
ORDER BY TaskParents.ID

内容的提问来源于stack exchange，提问作者V. Lernout