SQL Server递归CTE：为层级数据中的重复项添加标识

我来帮你解决递归CTE里给重复项加出现次数标识的问题！

核心思路是在递归遍历的每一步，跟踪当前节点在已生成的路径中出现的频次，再将这个频次作为后缀追加到节点名称后，以此区分重复项。结合你的场景，我整理了调整后的SQL代码，下面会逐步解释关键逻辑：

WITH RecursiveHierarchy AS (
    -- 锚点成员：处理所有根节点（ParentId IS NULL的节点）
    SELECT 
        Id,
        ParentId,
        Name,
        1 AS Level,
        CAST(Name AS VARCHAR(MAX)) AS Path,
        -- 根节点为首次出现，频次初始化为1
        1 AS Occurrence,
        -- 生成带标识的名称：名称 + "_首次次数"
        CAST(CONCAT(Name, '_', 1) AS VARCHAR(MAX)) AS NameWithOccurrence
    FROM Hierarchy
    WHERE ParentId IS NULL

    UNION ALL

    -- 递归成员：遍历子节点并计算重复频次
    SELECT 
        h.Id,
        h.ParentId,
        h.Name,
        rh.Level + 1 AS Level,
        -- 拼接完整路径
        CAST(CONCAT(rh.Path, ' > ', h.Name) AS VARCHAR(MAX)) AS Path,
        -- 计算当前节点在父路径中的已出现次数 + 1
        (LEN(rh.Path) - LEN(REPLACE(rh.Path, h.Name, ''))) / LEN(h.Name) + 1 AS Occurrence,
        -- 生成带频次标识的最终名称
        CAST(CONCAT(h.Name, '_', (LEN(rh.Path) - LEN(REPLACE(rh.Path, h.Name, ''))) / LEN(h.Name) + 1) AS VARCHAR(MAX)) AS NameWithOccurrence
    FROM Hierarchy h
    INNER JOIN RecursiveHierarchy rh ON h.ParentId = rh.Id
)
-- 输出最终结果
SELECT 
    Id,
    Level,
    Path,
    NameWithOccurrence,
    Occurrence
FROM RecursiveHierarchy
ORDER BY Level, Id;

关键逻辑解释 #

1. 锚点成员初始化：
   根节点作为遍历起点，默认是首次出现，所以直接将Occurrence设为1，同时生成带_1后缀的标识名称。

2. 递归频次计算：
   使用(LEN(rh.Path) - LEN(REPLACE(rh.Path, h.Name, ''))) / LEN(h.Name)统计父路径中当前节点名称的已出现次数：

   • 用原路径长度减去替换掉当前节点名称后的路径长度，得到的差值除以节点名称的长度，就是已出现的次数
   • 再加1就是当前节点的本次出现频次，最后拼接成带标识的名称

灵活调整提示 #

• 如果需要基于Id而非Name判断重复（比如同名但不同Id的节点不算重复），只需要把统计逻辑里的h.Name换成CAST(h.Id AS VARCHAR)即可
• 如果节点名称包含特殊字符或和路径分隔符冲突，建议更换更独特的路径分隔符（比如|），或者改用XML拆分路径的方式统计频次，避免统计误差

这样修改后，你就能在正常遍历层级、跟踪路径的同时，为每个重复出现的节点添加唯一的次数标识啦！

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