如何递归导出相关行的子集？

本文目录导读：

1. 使用递归CTE (PostgreSQL/MySQL 8+)
2. 递归导出所有子节点
3. 导出所有祖先节点（向上追溯）
4. Excel/CSV导出示例
5. Python实现递归导出
6. 带过滤条件的递归导出
7. 使用游标递归（SQL Server）
8. 重要注意事项：

我来详细解释递归导出相关行子集的几种常见方法。

使用递归CTE (PostgreSQL/MySQL 8+)

-- 示例：导出组织层级关系
WITH RECURSIVE org_tree AS (
    -- 锚点查询：获取顶层节点
    SELECT 
        id,
        name,
        parent_id,
        1 AS level,
        CAST(id AS VARCHAR(100)) AS path
    FROM organizations
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归查询：获取子节点
    SELECT 
        o.id,
        o.name,
        o.parent_id,
        t.level + 1,
        CAST(t.path || '->' || o.id AS VARCHAR(100))
    FROM organizations o
    INNER JOIN org_tree t ON o.parent_id = t.id
)
SELECT * FROM org_tree
ORDER BY path;

递归导出所有子节点

-- 从指定节点开始，导出所有后代
WITH RECURSIVE descendants AS (
    -- 锚点：起始节点
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE id = 5  -- 根节点ID
    UNION ALL
    -- 递归：查找子节点
    SELECT c.id, c.name, c.parent_id, d.depth + 1
    FROM categories c
    INNER JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants;

导出所有祖先节点（向上追溯）

-- 从指定节点开始，导出所有祖先
WITH RECURSIVE ancestors AS (
    -- 锚点：起始节点
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE id = 20  -- 当前节点ID
    UNION ALL
    -- 递归：查找父节点
    SELECT c.id, c.name, c.parent_id, a.depth + 1
    FROM categories c
    INNER JOIN ancestors a ON c.id = a.parent_id
)
SELECT * FROM ancestors;

Excel/CSV导出示例

-- 导出为CSV格式，包含层级信息
WITH RECURSIVE org_export AS (
    SELECT 
        id,
        name,
        parent_id,
        1 AS level,
        name AS full_path
    FROM departments
    WHERE parent_id IS NULL
    UNION ALL
    SELECT 
        d.id,
        d.name,
        d.parent_id,
        o.level + 1,
        o.full_path || ' / ' || d.name
    FROM departments d
    INNER JOIN org_export o ON d.parent_id = o.id
)
SELECT 
    REPEAT('  ', level - 1) || name AS hierarchy,
    level,
    full_path
FROM org_export
ORDER BY full_path;

Python实现递归导出

import pandas as pd
def get_related_rows(df, root_id, parent_col='parent_id', id_col='id'):
    """递归获取所有相关行"""
    related = []
    def recurse(parent_id):
        children = df[df[parent_col] == parent_id]
        for _, row in children.iterrows():
            related.append(row)
            recurse(row[id_col])
    # 添加根节点
    root = df[df[id_col] == root_id]
    if not root.empty:
        related.append(root.iloc[0])
        recurse(root_id)
    return pd.DataFrame(related)
# 使用示例
df = pd.DataFrame({
    'id': [1, 2, 3, 4, 5],
    'name': ['A', 'B', 'C', 'D', 'E'],
    'parent_id': [None, 1, 1, 2, 3]
})
result = get_related_rows(df, 1)
print(result)

带过滤条件的递归导出

-- 只导出符合条件的子集
WITH RECURSIVE filtered_tree AS (
    -- 锚点：找到所有满足条件的根节点
    SELECT id, name, parent_id, status, 1 AS level
    FROM products
    WHERE status = 'active' AND parent_id IS NULL
    UNION ALL
    -- 递归：只包含激活状态的节点
    SELECT p.id, p.name, p.parent_id, p.status, t.level + 1
    FROM products p
    INNER JOIN filtered_tree t ON p.parent_id = t.id
    WHERE p.status = 'active'
)
SELECT * FROM filtered_tree;

使用游标递归（SQL Server）

-- SQL Server 递归CTE
WITH EmployeeHierarchy AS (
    -- 锚点
    SELECT 
        EmployeeID,
        ManagerID,
        FirstName,
        LastName,
        0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    -- 递归
    SELECT 
        e.EmployeeID,
        e.ManagerID,
        e.FirstName,
        e.LastName,
        h.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy h ON e.ManagerID = h.EmployeeID
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, LastName, FirstName;

重要注意事项：

1. 性能优化：递归查询可能影响性能，建议添加索引
2. 深度限制：设置最大递归深度防止无限循环
3. 循环检测：确保数据结构没有循环引用
4. 数据量：大量数据时考虑分批处理

-- 添加深度限制和循环检测
WITH RECURSIVE safe_tree AS (
    -- 锚点
    SELECT id, name, parent_id, 1 AS depth, 
           ARRAY[id] AS visited_ids
    FROM nodes
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归，带深度限制和循环检测
    SELECT n.id, n.name, n.parent_id, 
           t.depth + 1,
           t.visited_ids || n.id
    FROM nodes n
    INNER JOIN safe_tree t ON n.parent_id = t.id
    WHERE t.depth < 20  -- 深度限制
      AND NOT n.id = ANY(t.visited_ids)  -- 循环检测
)
SELECT * FROM safe_tree;

这样就能安全高效地递归导出相关行的子集了。