有一個看起來像這樣的樹結構：
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 查詢來實作這一點
uj5u.com熱心網友回復：
您需要在 UPDATE 陳述句中使用遞回 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;
請參閱演示。
uj5u.com熱心網友回復：
使用遞回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 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/463028.html
標籤：sql 数据库 PostgreSQL sql更新 公共表表达式
上一篇：美麗湯。2個問題：find_all回傳空串列以及如何遍歷封閉容器
下一篇：視窗功能需要分組嗎？