在项目中,经常会遇到遍历整个层级结构直到顶层这种情况,我们通常会在业务层通过代码进行处理,今天分享一种SQL写法,直接遍历整个层级结构直到顶层的结构。
(1)、表结构示例
本次测试的表为机构层级表名dept,表结构大致如下图:
(2)、查询语句编写
假设我们要查询的当前数据ID为:a4202e4153a541afa3462675d6c86cca,则写法如下:
WITH RECURSIVE DeptHierarchy AS (
-- Anchor member: 选择起始点
SELECT
id,
name,
parentid,
ARRAY[id::text] AS path, -- 显式地将id转换为text并创建数组
1 AS level -- 当前节点的层级
FROM
department
WHERE
id = 'a4202e4153a541afa3462675d6c86cca' -- 替换为起始的部门ID
UNION ALL
-- Recursive member: 递归地连接父级
SELECT
d.id,
d.name,
d.parentid,
dh.path || d.id::text, -- 将当前节点ID(转换为text)添加到路径数组的末尾
dh.level + 1
FROM
department d
JOIN DeptHierarchy dh ON d.id = dh.parentid -- 连接当前层级与上一层级的CTE结果
)
SELECT id,name,path,level FROM DeptHierarchy;
注意,我在ARRAY[id::text] AS path
和dh.path || d.id::text
中使用了::text
来确保id
被转换为text
类型,这是因为数组的元素类型需要一致。如果id
已经是text
类型,则不需要这种转换。但是,由于我们使用的是UUID格式的字符串作为ID,因此转换为text
是安全的。
(3)、查看结果
ID为a4202e4153a541afa3462675d6c86cca的层级查询祖先级,已知晓其只有上一个层级,查询结果展示如下:
数据排序依次往上,直至顶层。有了这个结果,那么在处理遍历的时候,就可以手到擒来了!