如何用SQL子查询实现父子级目录结构的层级查询？

递归CTE可查询无限层级目录结构，需表中含id和parent_id字段且根节点parent_id为NULL；PostgreSQL、SQL Server、SQLite 3.8.3+和MySQL 8.0+支持，旧版MySQL需应用层模拟。

用 WITH RECURSIVE 查询无限层级目录结构

PostgreSQL、SQL Server、SQLite 3.8.3+ 和 MySQL 8.0+ 支持递归 CTE，这是查父子目录最直接的方式。不支持的数据库（如旧版 MySQL）必须用应用层拼接或多次查询模拟。

关键点在于：父节点和子节点必须在同张表里（比如 categories 表含 id 和 parent_id 字段），且根节点的 parent_id 为 NULL 或 0。

WITH RECURSIVE tree AS (  SELECT id, name, parent_id, 1 AS level  FROM categories  WHERE parent_id IS NULL  -- 根节点  UNION ALL  SELECT c.id, c.name, c.parent_id, t.level + 1  FROM categories c  INNER JOIN tree t ON c.parent_id = t.id)SELECT * FROM tree ORDER BY level, id;

• level 字段可用来缩进显示或限制深度（加 WHERE level ）
• MySQL 8.0+ 默认递归深度限制为 1000，超限会报错 ERROR 3636，需调大 cte_max_recursion_depth
• PostgreSQL 中若出现循环引用（A→B→A），会报错 infinite recursion detected，建议加 cycle 子句（但不是所有数据库都支持）

MySQL 5.7 及更早版本只能靠多次 JOIN 模拟有限层级

没有递归 CTE 时，最多能查固定几级，比如三级目录就写三表 JOIN，四级就得四次 JOIN —— 不灵活，且容易漏数据或笛卡尔积爆炸。

典型写法是自连接，每层对应一次 LEFT JOIN：

SELECT   t1.name AS level1,  t2.name AS level2,  t3.name AS level3FROM categories t1LEFT JOIN categories t2 ON t2.parent_id = t1.idLEFT JOIN categories t3 ON t3.parent_id = t2.idWHERE t1.parent_id IS NULL;

• 只适用于层级明确且稳定（比如“省-市-区”固定三级）
• LEFT JOIN 是为了保留中间某层为空的情况（如一级目录下无二级）
• 性能随 JOIN 数量指数下降，超过 4 层基本不可用；索引必须建在 parent_id 上，否则全表扫描
• 无法返回统一字段结构（比如每行都是 id/name/level），前端解析麻烦

子查询嵌套写法只适合单路径追溯（比如查某个目录的全部祖先）

如果目标只是“给定一个 id，找出它所有上级目录”，用多层子查询比递归更兼容，也更易理解。

例如查 id = 100 的完整路径（从根到它自己）：

SELECT * FROM categoriesWHERE id IN (  SELECT parent_id FROM categories WHERE id = 100  UNION ALL  SELECT parent_id FROM categories WHERE id IN (    SELECT parent_id FROM categories WHERE id = 100  )  UNION ALL  SELECT parent_id FROM categories WHERE id IN (    SELECT parent_id FROM categories WHERE id IN (      SELECT parent_id FROM categories WHERE id = 100    )  ));

• 这种写法本质是手动展开递归，层数完全由 SQL 长度决定，维护成本高
• 不能保证顺序（谁是第一级谁是第二级），需要额外用 ORDER BY 或应用层排序
• 比起 WITH RECURSIVE，它无法自然表达“向下找子节点”，只适合向上溯源
• Oracle 用户可能习惯用 CONNECT BY PRIOR，但那是方言，跨库不可移植

应用层组装仍是很多团队的实际选择

当数据库不支持递归、层级动态变化、或需配合权限过滤时，一次性查出全部目录再用代码组装树结构，反而更可控。

核心思路：一次 SELECT * 拿全量，然后按 parent_id 建哈希映射，遍历构建树：

// 伪代码示例（Python）rows = db.execute("SELECT id, name, parent_id FROM categories")nodes = {r['id']: {'id': r['id'], 'name': r['name'], 'children': []} for r in rows}roots = []for r in rows:    if r['parent_id'] is None:        roots.append(nodes[r['id']])    else:        nodes[r['parent_id']]['children'].append(nodes[r['id']])

• 比多次查询快，比复杂 SQL 更易 debug
• 可轻松加入业务逻辑：比如跳过被禁用的节点、按用户权限裁剪分支
• 注意空 parent_id 类型：有的库存 NULL，有的存 0，代码要对齐
• 如果目录量极大（10 万+ 行），内存和序列化开销需评估，此时还是得回数据库做分层加载

实际项目里，递归 CTE 是首选，但得确认数据库版本和运维是否允许开启相关参数；老系统迁移到新版本前，应用层组装往往是最省事的过渡方案。真正容易被忽略的是循环引用检测——测试数据随手设错一个 parent_id，就能让整个树查询卡死或报错。