SQL子查询实现父子目录结构层级查询

在数据库开发中，处理父子级目录结构一直是个经典命题——无论是网站的分类树、组织架构图，还是任意一种具有层级关系的数据，查询起来都不是简单的 SELECT * FROM table 能搞定的。要玩转递归CTE，数据库表里必须得有 id 和 parent_id 这两个字段，而且根节点的 parent_id 还得是 NULL —— 这是个硬性条件。好消息是，PostgreSQL、SQL Server、SQLite 3.8.3+ 和 MySQL 8.0+ 已经原生支持了递归CTE，旧版MySQL的用户则只能在应用层想办法模拟。

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

对于支持递归CTE的数据库，这无疑是最高效、最自然的选择。思路其实很简单：先找出所有根节点（parent_id IS NULL），然后一层一层往下“递归”地把子节点接上去。

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 <= 3）。
• MySQL 8.0+ 默认递归深度限制是1000，如果数据层级过深，会直接报 ERROR 3636。遇到这种情况，需要调大 cte_max_recursion_depth 这个参数。
• PostgreSQL 在处理循环引用（比如A的parent是B，B的parent又是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数量指数级下降，超过四层基本就扛不住了。必须要在 parent_id 字段上建索引，否则全表扫描会炸掉。
• 还有一个很头疼的问题：返回的字段结构不规整（每层一个字段），前端解析起来非常麻烦，也无法统一按照 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更容易调试。
• 可以非常轻松地嵌入业务逻辑，比如跳过被禁用的节点、按用户权限裁剪不需要的分支。
• 需要特别留意 parent_id 的数据类型——有些系统存的是 NULL，有些存的是 0，代码逻辑必须对齐。
• 如果目录数据量非常大（比如10万行以上），内存和序列化开销就需要提前评估。这时候还是回到数据库做分层加载更稳妥。

实际项目里，递归CTE通常是首选，但前提是确认数据库版本以及运维是否允许开启相关参数。老系统在迁移到新版本之前，应用层组装往往是最省事的过渡方案。一个容易被忽视的风险是循环引用——测试数据里随手设错一个 parent_id，就可能导致整个树查询卡死或者报错。