Question

有一个看起来像这样的树结构：

                                 root
                   A                               B
          A1           A2                     B1        B2
      A1.1  A1.2      A2.1                   B1.1

桌子看起来像这样：

id | name |value | parent_id
1    root   null    null
2    A      null    1
3    B      null    1    
4    A1     null    2
5    A1.1   2       4
6    A1.2   3       4
7    A2     null    2
8    A2.1   5       7
9    B1     null    3
10   B2     1       3
11   B1.1   10      9   
.........................

所有非叶子节点都必须包含其孩子的总和，而不仅仅是根节点。例如，这就是我所需的输出的样子：

id | name |value | parent_id
1    root   21      null
2    A      10      1
3    B      11      1    
4    A1     5       2
5    A1.1   2       4
6    A1.2   3       4
7    A2     5       2
8    A2.1   5       7
9    B1     10      3
10   B2     1       3
11   B1.1   10      9

如何使用快速的postgres查询来实现此目标

Answer 1

您需要在更新语句中进行递归CTE：

UPDATE tablename AS t
SET "value" = c.value
FROM (
  WITH RECURSIVE cte AS(
    SELECT t1.id, t1.name, t1.value, t1.parent_id
    FROM tablename t1
    WHERE NOT EXISTS (SELECT 1 FROM tablename t2 WHERE t2.parent_id = t1.id)
    UNION ALL
    SELECT t.id, t.name, c.value, t.parent_id
    FROM tablename t INNER JOIN cte c 
    ON c.parent_id = t.id
  )
  SELECT id, SUM("value") "value"
  FROM cte
  GROUP BY id
) c 
WHERE c.id = t.id;

请参阅

Answer 2

使用递归cte：

with recursive cte(id, name, val, p) as (
   select t.id, t.name, t.value, '.'||(select t1.id from tbl t1 where t1.parent_id is null)||'.'||t.id||'.' from tbl t where t.parent_id = (select t1.id from tbl t1 where t1.parent_id is null)
   union all
   select t.id, t.name, t.value, c.p||t.id||'.' from cte c join tbl t on c.id = t.parent_id
)
select t.id, sum(case when c.val is null then 0 else c.val end) from tbl t 
join cte c on c.p like '%.'||t.id||'.%' group by t.id order by t.id

---

要使用总和的子节点值更新原始表，您可以在update> update> update中使用子查询：

update tbl set value = t1.val from (
   select t.id tid, sum(case when c.val is null then 0 else c.val end) val from tbl t 
   join cte c on c.p like '%.'||t.id||'.%' group by t.id) t1 
where t1.tid = id