Postgres查询以获取所有子代ID [英] Postgres query to get all the children ids

问题描述

我是SQL新手，到目前为止只写了非常基本的查询。

我有一个看起来像这样的表

  item_full_name varchar（65535）
 item_id bigint 
 item_owners varchar（255）
 item_approver_group varchar（255）
 item_state varchar （255）
 item_parent_id bigint 
 item_children varchar（65535）
  

最初item_children是所有行均为空，但每个项目都有一个item_parent_id，并且不为null。我想编写一个查询，查看所有行&

例如。

<$对应的父ID，并用逗号分隔的一串子ID更新每行的item_children。 p $ p> item_full_name | item_id | item_owners | item_parent_id | item_children
item1 | 1 | o1，o2 | 2 |
item2 | 3 | owner8 | 2 |
item3 | 2 | owner6 | 0 |
item4 | 4 | owner7 | 1 |


应将其转换为

  item_full_name | item_id | item_owners | item_parent_id | item_children 
 item1 | 1 | o1，o2 | 2 | 4 
 item2 | 3 | owner8 | 2 | 
 item3 | 2 | owner6 | 0 | 3,1 
 item4 | 4 | owner7 | 1 | 
  

任何指针都会有所帮助。谢谢！

解决方案

我开始走递归CTE的道路，但后来意识到您只想要每个孩子的孩子父级，在单个级别上。一种方法是通过 item_parent_id 汇总 item_id 。然后，将原始表格加入此结果中，以获取每个父母的孩子的CSV列表。

 与cte AS（
选择item_parent_id，STRING_AGG（item_id :: text，'，'）AS item_children 
从yourTable 
 GROUP BY item_parent_id 
）
 
 SELECT 
 t1 .item_full_name，
 t1.item_id，
 t1.item_owners，
 t1.item_parent_id，
 t2.item_children 
 from yourTable t1 
左联接cte t2 
 on t1.item_id = t2.item_parent_id 
 OR BY BY 
 t1.item_full_name; 
  

演示

I'm an SQL noob and wrote only very basic queries so far.

I have a table that looks like this

item_full_name      varchar(65535)
item_id             bigint
item_owners         varchar(255)
item_approver_group varchar(255)
item_state          varchar(255)
item_parent_id      bigint
item_children       varchar(65535)

Initially item_children is empty for all the rows but each item has a item_parent_id and is not null. I want to write a query that looks at all the rows & corresponding parent ids and updates each row's item_children with a string of children ids separated by comma.

for eg.

item_full_name | item_id | item_owners | item_parent_id | item_children
item1          | 1       | o1, o2      | 2              | 
item2          | 3       | owner8      | 2              |
item3          | 2       | owner6      | 0              |
item4          | 4       | owner7      | 1              |

This should be transformed to

item_full_name | item_id | item_owners | item_parent_id | item_children
item1          | 1       | o1, o2      | 2              | 4
item2          | 3       | owner8      | 2              |
item3          | 2       | owner6      | 0              | 3,1
item4          | 4       | owner7      | 1              |

Any pointers would be helpful. Thanks!

解决方案

I started off going down the road of a recursive CTE, but then realized that you just want the children of each parent, at that single level. One approach is to aggregate the item_id by item_parent_id. Then, join your original table to this result to obtain the CSV list of children for each parent.

WITH cte AS (
    SELECT item_parent_id, STRING_AGG(item_id::text, ',') AS item_children
    FROM yourTable
    GROUP BY item_parent_id
)

SELECT
    t1.item_full_name,
    t1.item_id,
    t1.item_owners,
    t1.item_parent_id,
    t2.item_children
FROM yourTable t1
LEFT JOIN cte t2
    ON t1.item_id = t2.item_parent_id
ORDER BY
    t1.item_full_name;

Demo

这篇关于Postgres查询以获取所有子代ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！