如何编写单表查询以遍历多级文件夹及文档结构？

针对你描述的目录表结构（含SYSID、NODE_TYPE、PARENT等字段），要实现指定文件夹及其所有子层级的文件夹、文档查询，以下是两种可靠方案：

方法一：递归CTE（推荐，无游标） #

绝大多数现代关系型数据库（SQL Server、PostgreSQL、MySQL 8.0+等）支持递归公共表表达式（CTE），这是处理无限层级遍历最简洁高效的方式，无需担心死循环（可通过参数限制递归深度）。

示例SQL #

假设要查询SYSID = 'TARGET_FOLDER_ID'的文件夹及其所有子内容：

WITH RecursiveDir AS (
    -- 锚点成员：初始指定的目标文件夹
    SELECT 
        SYSID,
        NODE_TYPE,
        PARENT,
        ISFIRST,
        NEXT,
        DOCNO,
        -- 可选：记录当前层级，方便区分目录深度
        1 AS Level
    FROM YourTableName
    WHERE SYSID = 'TARGET_FOLDER_ID'
    
    UNION ALL
    
    -- 递归成员：遍历所有子节点（文件夹+文档）
    SELECT 
        t.SYSID,
        t.NODE_TYPE,
        t.PARENT,
        t.ISFIRST,
        t.NEXT,
        t.DOCNO,
        rd.Level + 1 AS Level
    FROM YourTableName t
    INNER JOIN RecursiveDir rd ON t.PARENT = rd.SYSID
    -- 可选：防止循环引用导致无限递归（如果存在非法的循环目录结构）
    WHERE t.SYSID NOT IN (SELECT SYSID FROM RecursiveDir)
)
-- 最终查询所有层级的内容
SELECT * FROM RecursiveDir
-- 可选：限制最大递归深度，避免意外死循环（比如SQL Server默认100，可调整）
OPTION (MAXRECURSION 0); -- 0表示不限制，根据实际情况设置

说明 #

• 锚点成员定位初始文件夹，递归成员通过PARENT = rd.SYSID关联所有子节点，自动遍历无限层级。
• 加入WHERE t.SYSID NOT IN (SELECT SYSID FROM RecursiveDir)可避免因数据错误导致的循环引用（如A的父是B，B的父是A）。
• MAXRECURSION参数可控制最大递归深度，防止意外的无限递归。

方法二：游标+循环（避免死循环版） #

如果必须使用游标，核心是记录已处理的文件夹ID，确保每个文件夹只被遍历一次，从根源避免死循环。

示例SQL #

-- 创建临时表存储最终结果
CREATE TABLE #DirResult (
    SYSID VARCHAR(50),
    NODE_TYPE CHAR(1),
    PARENT VARCHAR(50),
    ISFIRST BIT,
    NEXT VARCHAR(50),
    DOCNO VARCHAR(50),
    Level INT
)

-- 创建临时表记录已处理的文件夹（防止重复遍历）
CREATE TABLE #ProcessedFolders (
    SYSID VARCHAR(50) PRIMARY KEY,
    IsProcessed BIT DEFAULT 0
)

-- 初始化：插入目标文件夹
INSERT INTO #ProcessedFolders (SYSID) VALUES ('TARGET_FOLDER_ID')
INSERT INTO #DirResult
SELECT SYSID, NODE_TYPE, PARENT, ISFIRST, NEXT, DOCNO, 1
FROM YourTableName WHERE SYSID = 'TARGET_FOLDER_ID'

-- 循环处理未遍历的文件夹
WHILE EXISTS (SELECT 1 FROM #ProcessedFolders WHERE IsProcessed = 0)
BEGIN
    -- 声明游标，取未处理的文件夹
    DECLARE @CurrentFolderID VARCHAR(50)
    DECLARE FolderCursor CURSOR FOR
        SELECT SYSID FROM #ProcessedFolders WHERE IsProcessed = 0

    OPEN FolderCursor
    FETCH NEXT FROM FolderCursor INTO @CurrentFolderID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 插入当前文件夹的所有子节点（文件夹+文档）
        INSERT INTO #DirResult
        SELECT 
            t.SYSID,
            t.NODE_TYPE,
            t.PARENT,
            t.ISFIRST,
            t.NEXT,
            t.DOCNO,
            dr.Level + 1
        FROM YourTableName t
        INNER JOIN #DirResult dr ON t.PARENT = dr.SYSID AND dr.SYSID = @CurrentFolderID

        -- 将当前文件夹下的子文件夹标记为待处理（如果是文件夹）
        INSERT INTO #ProcessedFolders (SYSID)
        SELECT SYSID
        FROM YourTableName
        WHERE PARENT = @CurrentFolderID AND NODE_TYPE = 'F'
        AND SYSID NOT IN (SELECT SYSID FROM #ProcessedFolders)

        -- 标记当前文件夹为已处理
        UPDATE #ProcessedFolders SET IsProcessed = 1 WHERE SYSID = @CurrentFolderID

        FETCH NEXT FROM FolderCursor INTO @CurrentFolderID
    END

    CLOSE FolderCursor
    DEALLOCATE FolderCursor
END

-- 查询最终结果
SELECT * FROM #DirResult

-- 清理临时表
DROP TABLE #DirResult
DROP TABLE #ProcessedFolders

说明 #

• #ProcessedFolders临时表记录所有已发现的文件夹，IsProcessed标记是否已遍历其子节点，确保每个文件夹只被处理一次。
• 每次循环仅处理未标记的文件夹，遍历完成后立即标记为已处理，彻底避免重复遍历导致的死循环。

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