英文:
Is recursive query an option for concatenating text for building a path
问题
| id | name | path |
|---|---|---|
| 1 | container1 | /container1 |
| 2 | container2 | /container2 |
| 3 | subcontainer1 | /container2/subcontainer1 |
| 4 | container4 | /container4 |
| 5 | subsub | /container2/subcontainer1/subsub |
可以尽量将此任务交给数据库执行，但是我缺乏 SQL 技能。如果不可能，我会尝试在应用程序内完成。这可以在 SQL 中实现吗？感谢！
英文:
Simplifying a bit. I have the following data structure:
| id | name | traversal_ids[] || -------- | -------------- |-----------------|| 1 | container1 | {1} || 2 | container2 | {2} || 3 | subcontainer1 | {2, 3} || 4 | container4 | {4} || 5 | subsub | {2 ,3 ,5} |
and I'd like to transform this in the following:
| id | name | path ||-------- |-------------- |----------------------------------|| 1 | container1 | /container1 || 2 | container2 | /container2 || 3 | subcontainer1 | /container2/subcontainer1 || 4 | container4 | /container4 || 5 | subsub | /container2/subcontainer1/subsub |
I'd like as much as possible to give this to the database to execute, but unfortunately I lack the sql skills. If it's not possible I'll try to get this done inside the app.
Can this be achieved in SQL?
Thanks!
答案1
得分: 2
不需要递归。我们可以在一个侧面连接中展开数组，通过连接将相应的名称带回，然后再聚合：
select t.*, x.*from mytable tcross join lateral (select string_agg(t1.name, '/' order by x.ord) pathfrom unnest(t.traversal_ids) with ordinality x(id, ord)inner join mytable t1 on t1.id = x.id) x
英文:
No need for recursion. We can unnest the array in a lateral join, bring the corresponding names with a join, then aggregate back:
select t.*, x.*from mytable tcross join lateral (select string_agg(t1.name, '/' order by x.ord) pathfrom unnest(t. traversal_ids) with ordinality x(id, ord)inner join mytable t1 on t1.id = x.id) x
答案2
得分: 0
with cte as (
select t2.id, t2.name, t1.name as sub_name, t1.id as sub_id
from mytable t1
inner join mytable t2 on t1.id = ANY(t2.traversal_ids)
)
select id, name, '/' || string_agg(sub_name, '/' order by sub_id) as path
from cte
group by id, name
英文:
Assuming the traversal ids are always with ascendant order, You can do it using a simple inner join with ANY operator as follows :
with cte as (select t2.id, t2.name, t1.name as sub_name, t1.id as sub_idfrom mytable t1inner join mytable t2 on t1.id = ANY(t2.traversal_ids))select id, name, '/' || string_agg(sub_name, '/' order by sub_id) as pathfrom ctegroup by id, name
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论