SQL深入理解递归查询与层级结构的处理：以拼接ID路径为例
SQL深入理解递归查询与层级结构的处理：以拼接ID路径为例
一、前言
在许多实际业务场景中，我们需要处理具有层级关系的数据。例如，组织架构、产品分类、菜单结构等，往往以树形结构存储在数据库中。而在进行层级数据查询时，如何将每个节点的父节点信息串联起来，形成从根节点到当前节点的完整路径，是一个常见的问题。
本文将通过一个实际的例子，详细介绍如何使用递归查询和 SQL 字符串拼接技巧来解决这个问题，特别是如何将最上级 ID 拼接到当前 ID，形成以 ; 分隔的路径。
1. 层级结构数据的存储
首先，我们假设有一个简单的表格 hga_fcwh，用来存储一些层级关系数据。该表包含以下字段：
- yuan_shi_id：每个节点的唯一标识。
- quan_cheng：节点的全称。
- jian_cheng：节点的简称。
- shang_yi_ji：父节点的
yuan_shi_id，指向当前节点的上级。如果没有父节点，则值为NULL。
例如，表中的数据可能如下：
| yuan_shi_id | quan_cheng | jian_cheng | shang_yi_ji |
|---|---|---|---|
| 1 | 全称A | A | NULL |
| 2 | 全称B | B | 1 |
| 3 | 全称C | C | 1 |
| 4 | 全称D | D | 2 |
| 5 | 全称E | E | 3 |
从表中可以看出，节点 1 是顶级节点，节点 2 和节点 3 是节点 1 的子节点，节点 4 是节点 2 的子节点，节点 5 是节点 3 的子节点。
2. 问题的核心：拼接 ID 路径
我们需要实现一个查询，返回每个节点的 xiangxi_id，即拼接从根节点到当前节点的所有 ID，层级之间用 ; 分隔。例如，节点 4 的 xiangxi_id 应该是 1;2;4，而节点 5 的 xiangxi_id 应该是 1;3;5。
3. 解决方案：使用递归查询
SQL 提供了递归查询的功能，常用于处理树形结构。我们可以利用递归查询实现从每个节点回溯到根节点的操作，并且在递归过程中拼接 ID。
二、使用递归查询的基本思路
递归查询通常由两部分组成：
- 非递归部分：用于选择根节点（或层级最上级的记录）。
- 递归部分：通过连接父节点和子节点来向下遍历树形结构，同时进行路径拼接。
1. 实现步骤
我们通过 CTE（公共表表达式） 和递归查询来实现层级数据的拼接，以下是详细的 SQL 查询：
WITH FieldHierarchy AS (
-- 非递归部分：选择顶层记录
SELECT
yuan_shi_id,
quan_cheng,
jian_cheng,
shang_yi_ji,
CAST(yuan_shi_id AS VARCHAR(MAX)) AS xiangxi_id, -- 初始化为自身的ID
0 AS LEVEL
FROM hga_fcwh
WHERE shang_yi_ji IS NULL
UNION ALL
-- 递归部分：查找子节点并拼接最上级ID到当前ID
SELECT
f.yuan_shi_id,
f.quan_cheng,
f.jian_cheng,
f.shang_yi_ji,
CAST(h.xiangxi_id + ';' + CAST(f.yuan_shi_id AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS xiangxi_id, -- 拼接父ID到当前ID
h.LEVEL + 1 AS LEVEL
FROM hga_fcwh f
INNER JOIN FieldHierarchy h ON f.shang_yi_ji = h.yuan_shi_id
)
-- 最终查询
SELECT
yuan_shi_id,
jian_cheng,
quan_cheng,
shang_yi_ji,
xiangxi_id,
LEVEL
FROM FieldHierarchy
ORDER BY LEVEL, yuan_shi_id;
2. 关键点解析
-
非递归部分：
在递归查询的起始部分，我们选择顶级节点（shang_yi_ji IS NULL）。每个顶级节点的xiangxi_id被初始化为其自身的yuan_shi_id。 -
递归部分：
在递归部分，我们通过INNER JOIN将当前节点和父节点连接起来，然后拼接父节点的xiangxi_id和当前节点的yuan_shi_id。这样，逐层拼接出从根节点到当前节点的路径。路径中的各个 ID 通过;进行分隔。 -
LEVEL 字段：
我们为每个节点引入了LEVEL字段，表示该节点在树形结构中的层级。LEVEL从顶层节点开始为 0，逐层递增。
3. 示例结果
假设执行查询后的结果为：
| yuan_shi_id | jian_cheng | quan_cheng | xiangxi_id | LEVEL |
|---|---|---|---|---|
| 1 | A | 全称A | 1 | 0 |
| 2 | B | 全称B | 1;2 | 1 |
| 3 | C | 全称C | 1;3 | 1 |
| 4 | D | 全称D | 1;2;4 | 2 |
| 5 | E | 全称E | 1;3;5 | 2 |
如上所示，每个节点的 xiangxi_id 都包含了从根节点到该节点的所有 ID，且层级结构清晰。
4. 应用场景
-
组织架构：在企业中，员工有着明确的上下级关系，我们可以利用这种递归查询来获取员工的上级、下级以及整个组织结构路径。
-
产品分类：产品有多个层级的分类，递归查询可以帮助我们生成完整的分类路径，便于查询和展示。
-
菜单结构：网站或应用中的菜单通常有层级关系，可以通过递归查询获取完整的菜单路径，便于渲染菜单。
三、结语
通过本文，我们深入探讨了如何利用递归查询处理树形结构数据，并且通过拼接路径来实现从根节点到每个节点的完整标识。递归查询不仅能够有效处理层级数据，而且在解决类似问题时非常高效，尤其是当数据量较大时，可以大幅简化查询和计算过程。
对于不同的数据库系统，你可能需要根据具体的语法和函数（如 STRING_AGG、GROUP_CONCAT 等）做出适当调整，但核心思路是一致的。希望通过这篇文章，大家能够更好地理解和运用递归查询来处理复杂的层级数据。
原文地址：https://blog.csdn.net/weixin_46146718/article/details/143890609
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！