求助：SQL递归CTE实现虚拟文件系统重命名后文件归属查询

你的核心问题是没有处理文件夹的重命名链条——当前的递归逻辑只遍历了基础的父子关系，没有跟踪到Folder1被重命名为Folder2的关联，导致旧文件夹下的文件无法映射到新文件夹名称。

修正后的SQL代码 #

-- 创建临时表
CREATE TABLE #Objects (
    child VARCHAR(20),
    parent VARCHAR(20),
    ObjectType int
)

-- 插入测试数据
INSERT INTO #Objects (child,parent,ObjectType) VALUES ('Folder1',NULL,1)
INSERT INTO #Objects (child,parent,ObjectType) VALUES ('File1','Folder1',0)
INSERT INTO #Objects (child,parent,ObjectType) VALUES ('File2','Folder1',0)
INSERT INTO #Objects (child,parent,ObjectType) VALUES ('Folder2','Folder1',1)  -- Folder1重命名为Folder2
INSERT INTO #Objects (child,parent,ObjectType) VALUES ('File3','Folder2',0)

-- 递归查询获取文件对应的最新文件夹
;WITH FolderHierarchy AS (
    -- 锚点：初始化所有文件夹的最终名称为自身
    SELECT 
        child AS OriginalNode,
        child AS FinalFolder,
        parent,
        ObjectType
    FROM #Objects
    WHERE ObjectType = 1
    UNION ALL
    -- 递归：跟踪重命名链条，找到每个旧文件夹对应的最新子文件夹
    SELECT 
        fh.OriginalNode,
        o.child AS FinalFolder,
        o.parent,
        o.ObjectType
    FROM FolderHierarchy fh
    INNER JOIN #Objects o ON fh.FinalFolder = o.parent
    WHERE o.ObjectType = 1
),
-- 提取每个节点的最终最新文件夹名称
FinalFolderMapping AS (
    SELECT 
        OriginalNode,
        FinalFolder,
        ROW_NUMBER() OVER (PARTITION BY OriginalNode ORDER BY (SELECT NULL) DESC) AS rn
    FROM FolderHierarchy
)
-- 关联文件与最新文件夹
SELECT 
    COALESCE(ffm.FinalFolder, o.parent) AS CurrentFolder,
    o.child AS FileName
FROM #Objects o
LEFT JOIN FinalFolderMapping ffm ON o.parent = ffm.OriginalNode AND ffm.rn = 1
WHERE o.ObjectType = 0;

-- 清理临时表
DROP TABLE #Objects

代码说明 #

1. FolderHierarchy CTE：递归遍历所有文件夹的重命名关系，生成旧文件夹到最新文件夹的映射链条（比如Folder1 → Folder2）。
2. FinalFolderMapping CTE：对每个旧文件夹，筛选出递归链条中最后一条记录（即最新的重命名结果）。
3. 最终查询：将文件的父文件夹与映射表关联，替换为最新的文件夹名称，得到所有文件的当前所属文件夹。

输出结果 #

CurrentFolder  FileName
Folder2        File1
Folder2        File2
Folder2        File3

原代码的问题分析 #

• 关联条件逻辑错误：a.child = c.parent应该是a.parent = c.child，但即使修正，也无法处理重命名场景。
• 未处理重命名链条：原递归仅遍历基础父子关系，没有跟踪文件夹的重命名记录，导致旧文件夹下的文件无法关联到新文件夹。

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