使用SQL递归CTE计算每个父节点的子树总和

修正后的表结构与数据 #

你提供的INSERT语句字段顺序与表结构不匹配，以下是修正后的建表和插入数据SQL：

CREATE TABLE coa (
    id INT(11) NOT NULL,
    parent INT(11) NULL DEFAULT NULL,
    amount DECIMAL(15,2) NULL DEFAULT 0,
    is_group INT(11),
    account_name VARCHAR(255),
    PRIMARY KEY (`id`)
);

INSERT INTO coa (id, parent, amount, is_group, account_name)
VALUES
(1, NULL, 0.0,   1, 'root'),
(2, 1,    0.0,   1, 'assets'),
(3, 1,    0.0,   1, 'curr_assets'),
(4, 3,    10.0,  0, 'cash'),
(5, 3,    20.0,  0, 'bank'),
(6, 1,    0.0,   1, 'fixed_assets'),
(7, 6,    100.0, 0, 'buildings'),
(8, 1,    30.0,  0, 'stocks'),
(9, 1,    40.0,  0, 'furnitures');

跨PostgreSQL/MySQL的递归查询SQL #

以下SQL适用于MySQL 8.0+和PostgreSQL 9.4+版本，通过递归CTE遍历树形结构，计算每个节点的子树总和（包含自身金额）：

WITH RECURSIVE coa_hierarchy AS (
    -- 锚点：初始化所有节点，标记每个节点的根节点为自身
    SELECT 
        id, 
        parent, 
        amount, 
        id AS root_id
    FROM coa
    UNION ALL
    -- 递归：关联子节点，继承父节点的根节点标记
    SELECT 
        c.id, 
        c.parent, 
        c.amount, 
        ch.root_id
    FROM coa c
    JOIN coa_hierarchy ch ON c.parent = ch.id
)
SELECT 
    root_id AS node_id,
    (SELECT account_name FROM coa WHERE id = root_id) AS node_name,
    SUM(amount) AS subtree_total
FROM coa_hierarchy
GROUP BY root_id
ORDER BY node_id;

预期结果 #

执行后将返回每个节点的ID、名称及对应子树的总金额：

可选调整 #

如果仅需计算分组节点（is_group=1）的子树总和，可在最后查询中添加过滤条件：

WITH RECURSIVE coa_hierarchy AS (
    SELECT 
        id, 
        parent, 
        amount, 
        id AS root_id
    FROM coa
    UNION ALL
    SELECT 
        c.id, 
        c.parent, 
        c.amount, 
        ch.root_id
    FROM coa c
    JOIN coa_hierarchy ch ON c.parent = ch.id
)
SELECT 
    root_id AS node_id,
    (SELECT account_name FROM coa WHERE id = root_id) AS node_name,
    SUM(amount) AS subtree_total
FROM coa_hierarchy
WHERE (SELECT is_group FROM coa WHERE id = root_id) = 1
GROUP BY root_id
ORDER BY node_id;

内容的提问来源于stack exchange，提问作者Dave