postgresql 如何从邻接表中获取结果[已关闭]

rlcwz9us  于 2022-12-29  发布在  PostgreSQL
关注(0)|答案(1)|浏览(94)

已关闭。此问题需要details or clarity。当前不接受答案。
**想要改进此问题?**添加详细信息并通过editing this post阐明问题。

2天前关闭。
Improve this question
我有如下表格。

organization_code parent_organization_code name           level
1                 0                        department1    department        
2                 1                        group1         group
3                 1                        group2         group
4                 2                        team1          team
5                 3                        team2          team
6                 3                        team3          team

我想将它们转换为以下结果
我想要的结果如下。

department  group  team
department1 group1 team1
department1 group2 team2
department1 group2 team3

将来的科系和级别会扩大,所以我想递归的得到结果。
如果有人有意见,请告诉我谢谢

dfddblmv

dfddblmv1#

尝试使用递归查询,如下所示:

with recursive cte as
(
  select organization_code, parent_organization_code, name, level, 
         ''::text group_, name department
  from table_name where parent_organization_code = 0
  union all
  select t.organization_code, t.parent_organization_code, t.name, t.level,
         c.name, c.department
  from table_name t join cte c
  on t.parent_organization_code=c.organization_code
)
select department, group_, name
from cte where level='team'

demo.

相关问题