我是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。我想编写一个查询,查看所有行&
例如。
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
发布评论