SQL：使用递归公共表达式(Recursive CTE)查询树形结构的数据

温_暖
745 阅读3分钟

计划更文3/100

  1. 根据根节点id查询树形结构数据
-- 根据根节点id查询树形结构
WITH T(ID, PARENT_ID) AS (
	SELECT ID, PARENT_ID FROM TABLE1 WHERE ID=1598 UNION ALL
	SELECT D.ID, D.PARENT_ID FROM T, TABLE1 D WHERE T.ID = D.PARENT_ID
)  SELECT * FROM T;

结果示例：

image.png

  1. 根据末级子节点id查询树形结构数据
-- 根据末级子节点查询树形结构
WITH T(ID, PARENT_ID) AS (
	SELECT ID, PARENT_ID FROM TABLE1 WHERE ID=1804 UNION ALL
	SELECT D.ID, D.PARENT_ID FROM T, TABLE1 D WHERE T.PARENT_ID = D.ID
)  SELECT * FROM T;

结果示例：

image.png

  1. 如何查询层级信息

为了查询层级指标，可以在Recursive CTE中添加一个额外的列“LEV”以跟踪层级。(注意，列名不要叫LEVEL，因为在ORACLE中，LEVEL是一个关键字，使用的话可以加双引号"LEVEL")

-- 根据根节点查询树形结构，根节点是LEV=1
WITH T(ID, PARENT_ID, LEV) AS (
	SELECT ID, PARENT_ID, 1 as LEV FROM TABLE1 WHERE ID=1598 UNION ALL
	SELECT D.ID, D.PARENT_ID, T.LEV+1 FROM T, TABLE1 D WHERE T.ID = D.PARENT_ID
)  SELECT * FROM T WHERE LEV=4;

结果示例：

image.png

-- 根据末级子节点查询树形结构，末级子节点的LEV=1 （每个level只有一条数据）
WITH T(ID, PARENT_ID, LEV) AS (
	SELECT ID, PARENT_ID, 1 as LEV FROM TABLE1 WHERE ID=1804 UNION ALL
	SELECT D.ID, D.PARENT_ID, T.LEV+1 FROM T, TABLE1 D WHERE T.PARENT_ID = D.ID
)  SELECT * FROM T WHERE LEV=1;

image.png

  1. 如何树状的查询一个递归结构的表
WITH T(id, name, parent_id, lev) AS (
    SELECT id, name, parent_id, 1 as lev
    FROM TABLE1
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name, d.parent_id, dt.lev + 1
    FROM T dt, TABLE1 d WHERE dt.id = d.parent_id 
)
SELECT LPAD(' ', lev * 4, ' ') || name AS name, id
FROM T
ORDER BY id;

image.png

在ORACLE中还可以使用如下代码进行递归查询：

SELECT * FROM TABLE1 WHERE LEVEL = 4 START WITH ID = '1940' CONNECT BY PRIOR ID = PARENT_ID;

  • 关于递归公共表达式(Recursive Common Table Expression，简称Recursive CTE):

    WITH T(ID, PARENT_ID, NAME, LEV) AS: 这是一个公共表表达式（CTE）的声明。它创建了一个名为“T”的临时结果集，并定义了四个字段：ID、PARENT_ID、NAME、LEV，其中LEV表示层级，将记录节点在层次结构中的位置。CTE是一种用于定义临时结果集的技术，可以在包含它的查询中使用。

    SELECT ID, PARENT_ID, NAME, 1 as LEV FROM TABLE1 WHERE ID=1598：这是CTE的基本部分，用于处理起始条件。

    UNION ALL：用于连接基本部分和递归部分的查询结果。UNION ALL允许有重复的行。

    SELECT D.ID, D.PARENT_ID, D.NAME, T.LEV+1 FROM T, TABLE1 D WHERE T.ID = D.PARENT_ID：这是CTE的递归部分。它从临时结果集“T”和“TABLE1”表中选择数据，此查询将当前层级的每个节点（用"T"表示）与其子节点（用"D"表示）进行匹配。当找到子节点时，层级值加1（通过"T.LEV+1"）。递归部分会在每个层级上执行，当递归部分找不到更多子节点时，递归查询停止。

    SELECT * FROM T WHERE LEV=4: 这是在CTE外部执行的主查询。它从临时结果集"T"中选择所有第四级（LEV=4）的节点。这意味着查询将返回从初始节点（ID=1598）开始的所有第四级子节点。

  • 这种递归查询适合什么样的表结构？

    递归查询通常适用于具有层次结构或树形结构的表。这类表中每个记录通常包含一个指向父节点的引用。这种结构可以表示组织、文件系统、产品类别等实体之间的关系。

    递归查询一个典型的示例是查询组织结构。表结构可能包括员工ID、员工姓名、职位和上级ID等字段，递归查询可帮助我们找到特定员工的所有下属、所有上级，或者计算每个员工在组织结构中的层级。

avatar
文章
阅读
粉丝