SQL查询：获取指定文件的祖先文件列表

针对你这种用prev_id关联的树形文件结构，用**递归CTE（Common Table Expression）**是最顺手的解决方案，它能自动向上遍历所有层级的前置文件，直到根节点（prev_id为NULL的文件）。

通用查询SQL（以查询file3的祖先为例） #

WITH RECURSIVE file_ancestors AS (
    -- 锚点：先定位到目标文件的直接前置文件
    SELECT id, filename, prev_id
    FROM files
    WHERE id = (SELECT id FROM files WHERE filename = 'file3')
    UNION ALL
    -- 递归：不断向上查找当前记录的前置文件
    SELECT f.id, f.filename, f.prev_id
    FROM files f
    JOIN file_ancestors fa ON f.id = fa.prev_id
)
-- 排除目标文件本身，只返回所有祖先
SELECT filename
FROM file_ancestors
WHERE filename != 'file3';

快速切换查询目标 #

如果要查file6的祖先，只需要把SQL里的'file3'改成'file6'就行，运行后会返回file5和file4。

代码逻辑说明 #

1. 递归CTE的组成：WITH RECURSIVE是MySQL 8.0及以上版本支持的语法，分为两部分：
   • 锚点成员：先通过文件名找到目标文件的ID，再定位它的直接前置文件
   • 递归成员：通过JOIN关联当前记录的prev_id和上一层的id，循环向上遍历，直到没有更多前置文件（prev_id为NULL时自动停止）
2. 最后一步筛选掉目标文件本身，只保留所有层级的祖先文件。

测试结果示例 #

查询file3时，返回结果：

filename
--------
file2
file1

查询file6时，返回结果：

filename
--------
file5
file4

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