PostgreSQL -用于管理器层次结构的递归CTE自联接
PostgreSQL -用于管理器层次结构的递归CTE自联接
提问于 2019-02-02 14:17:50
我在用PostgreSQL编写递归查询以显示员工之间的管理层次结构时遇到了麻烦。为了实现这一点，我需要将表自连接到表本身，直到每个员工到达其层次结构的末尾。我的数据在给定的一天是这样的：
+------------+-------------+-----------------+---------------------+
| date | employee_id | terminated_flag | manager_employee_id |
+------------+-------------+-----------------+---------------------+
| 2019-01-31 | 3 | 0 | 2 |
| 2019-01-31 | 2 | 1 | 1 |
| 2019-01-31 | 1 | 0 | |
+------------+-------------+-----------------+---------------------+理想情况下，我希望创建一个JSONB列，其中包含给定员工的完整层次结构和经理详细信息。我知道我可以通过递归附加到现有的JSONB列，但要做到这一点一直很困难。所需的输出将如下所示(删除列以便于阅读)：
+------------+-------------+-----------------------------------------------------------+
| date | employee_id | manager_hierarchy |
+------------+-------------+-----------------------------------------------------------+
| 2019-01-31 | 3 | {{"level":1,"id":2,"term":1},{"level":2,"id":1,"term":0}} |
| 2019-01-31 | 2 | {{"level":1,"id":1,"term":0}} |
| 2019-01-31 | 1 | |
+------------+-------------+-----------------------------------------------------------+在我的数据集中，从任何给定的员工到首席执行官可能有N个级别，所以一旦每个员工都到达首席执行官，递归就需要结束，首席执行官的manager_employee_id值将为空值。
这个是可能的吗？谢谢你的帮忙!
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-02-02 20:57:10
我认为这是一个递归CTE，用于获取层次结构，然后聚合以创建jsonb值：
with recursive t as (
select v.*
from (values (3, 2, 0), (2, 1, 1), (1, null, 0)) v(employee_id, manager_employee_id, terminated_flag)
),
cte as (
select distinct employee_id, manager_employee_id, terminated_flag, 1 as lev
from t
union all
select cte.employee_id, t.manager_employee_id, t.terminated_flag, lev + 1
from cte join
t
on cte.manager_employee_id = t.employee_id
)
select employee_id, jsonb_agg(jsonb_build_object('level', lev, 'id', manager_employee_id, 'terminated_flag', terminated_flag))
from cte
group by employee_id;Here是一个db<>fiddle。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54490543
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号