SQL 如何实现层级结构（树形数据）？-SQL-PHP中文网

SQL实现树形结构主要用递归查询，核心是WITH RECURSIVE（标准）或数据库特有语法；常用自引用表设计，含parent_id外键，根节点为NULL；需对parent_id和id建索引以提升递归查询性能。

sql 如何实现层级结构（树形数据）？

SQL 实现层级结构（树形数据）主要靠递归查询，核心是用 WITH RECURSIVE（标准 SQL）或数据库特有语法（如 SQL Server 的 CTE、Oracle 的 CONNECT BY、MySQL 8.0+ 的递归 CTE）。关键不在“怎么写”，而在“怎么设计”和“怎么查”。

用自引用表结构存树

最常见方式是单表加一个指向父节点的外键字段（比如 parent_id），根节点该字段为 NULL 或 0。

例如：

CREATE TABLE categories (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  parent_id INT NULL,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

这种结构简单、易维护，适合变动不频繁、深度适中的树（如菜单、分类、组织架构）。注意：要建好索引（parent_id 和 id 都建议索引），否则递归查询会很慢。

用递归 CTE 查任意层级路径

以 PostgreSQL / MySQL 8.0+ / SQL Server 为例，查某个节点的所有祖先或所有后代，都用 WITH RECURSIVE：

查某节点（id=5）的所有祖先（向上遍历）：

WITH RECURSIVE tree AS (
  -- 基础查询：从目标节点开始
  SELECT id, name, parent_id, 0 AS level
  FROM categories
  WHERE id = 5
<p>UNION ALL</p><div class="aritcle_card flexRow artxards">
											<div class="artcardd flexRow">
												<a class="aritcle_card_img" rel="nofollow" href="/xiazai/skill12218" title="win可用的mysql指令skill"><img
														src="https://img.php.cn/upload/skill/001/246/273/6adaf20d79f23518.jpg" alt="win可用的mysql指令skill" onerror="this.onerror='';this.src='/static/lhimages/moren/morentu.png'" ></a>
												<div class="aritcle_card_info flexColumn">
													<a rel="nofollow" href="/xiazai/skill12218" title="win可用的mysql指令skill" class="overflowclass">win可用的mysql指令skill</a>
													<p class="overflowclass">安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录，或需要将该能力封装成可公开复用的 Codex skill。</p>
												</div>
												<a rel="nofollow" href="/xiazai/skill12218" title="win可用的mysql指令skill" class="aritcle_card_btn flexRow flexcenter"><b></b><span>下载</span>
												</a>
											</div>
										</div><p>-- 递归部分：找 parent_id 对应的记录
SELECT c.id, c.name, c.parent_id, t.level + 1
FROM categories c
INNER JOIN tree t ON c.id = t.parent_id
)
SELECT * FROM tree ORDER BY level DESC;</p>

基础查询必须先命中起点（不能是空结果集）
递归部分的 JOIN 条件必须让结果逐步收敛（比如用 c.id = t.parent_id 向上找）
level 字段可用来排序或限制深度（加 WHERE t.level 防无限循环）

查子树（向下展开）并带缩进显示

展示完整子树时，常需要层级缩进。可在递归中拼接路径或生成空格：

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS level, CAST(name AS CHAR(200)) AS path
  FROM categories
  WHERE parent_id IS NULL  -- 根节点
<p>UNION ALL</p><p>SELECT c.id, c.name, c.parent_id, t.level + 1,
CONCAT(t.path, ' → ', c.name)
FROM categories c
INNER JOIN tree t ON c.parent_id = t.id
)
SELECT 
REPEAT('  ', level) || name AS indented_name,
id, parent_id, level
FROM tree
ORDER BY path;</p>

这样输出就自带层级视觉效果。注意字符串长度（CAST/CONCAT）和数据库函数差异（MySQL 用 CONCAT，PostgreSQL 用 ||，SQL Server 用 +）。

替代方案：闭包表 or 路径枚举

如果树频繁查询、少修改，且对性能敏感，可考虑预计算方案：

闭包表（Closure Table）：单独一张表存所有祖先-后代关系（ancestor, descendant, depth），查子树/祖先极快，但增删节点需批量更新关联行
路径枚举（Path Enumeration）：在节点里存完整路径（如 "/1/3/5"），用 LIKE 快速查子树（path LIKE '/1/3/%'），但路径更新成本高、不便于校验完整性

这两种不是“纯 SQL 树形查询”，而是用空间换时间的设计策略，适合读多写少场景。