这是我在Postgresql中遇到的问题的简化版本.
我有下表A：
[ID INTEGER | VALUE NUMERIC(10,2)|家长整数]
其中’PARENT’是列引用ID的自引用FK.
表定义是：
CREATE TABLE A(ID INTEGER IDENTITY,VALUE NUMERIC(10,2),PARENT INTEGER) ALTER TABLE A ADD CONSTRAINT FK FOREIGN KEY (PARENT) REFERENCES A(ID)
这个简单的表允许用户定义任意深度的树数据结构.现在我需要编写一个sql(我不想使用服务器端PL-sql)来报告每个节点,子树的总值“悬挂”在它下面.例如,使用下表：
| ID | VALUE | PARENT | ------------------------- | 1 | NULL | NULL | | 2 | 3.50 | 1 | | 3 | NULL | NULL | | 4 | NULL | 3 | | 5 | 1.50 | 4 | | 6 | 2.20 | 4 |
我应该得到以下结果集：
| ID | Total-Value-of-Subtree | | 1 | 3.50 | | 2 | 3.50 | | 3 | 3.70 | | 4 | 3.70 | | 5 | 1.50 | | 6 | 2.20 |
对于简单地,您可以假设只有叶节点具有值,非叶节点在VALUE列中始终具有NULL值.有没有办法在sql中执行此操作,甚至使用Postgresql特定的扩展？
在Postgresql中,您可以使用递归CTE(公用表表达式)在查询中遍历树.
以下是文档中的两个相关链接：
编辑
由于没有所需的子选择,它可能比Arion的查询在更大的数据集上运行得更好.
WITH RECURSIVE children AS (
-- select leaf nodes
SELECT id,value,parent
FROM t
WHERE value IS NOT NULL
UNION ALL
-- propagate values of leaf nodes up,adding rows
SELECT t.id,children.value,t.parent
FROM children JOIN t ON children.parent = t.id
)
SELECT id,sum(value)
FROM children
GROUP BY id -- sum up appropriate rows
ORDER BY id;