PostgreSQL中的层次和

发布时间：2020-12-13 16:17:14 所属栏目：百科来源：网络整理

导读：这是我在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 CONST

这是我在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(公用表表达式)在查询中遍历树.

以下是文档中的两个相关链接：

> Syntax
> Examples

编辑

由于没有所需的子选择,它可能比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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!