基于SQL表实现任意深度树结构的聚合查询

要实现任意深度树结构的节点自身+所有子节点Value总和计算，需要先处理原始表的重复节点，再通过递归遍历树来累加后代节点的值，具体方案如下：

第一步：预处理重复节点 #

原始表存在同一(name, parent)组合的重复行，先合并这些行的Value，得到每个节点的自身基础值：

-- 预处理：合并同一节点的重复值，得到每个节点的自身value
WITH node_base AS (
    SELECT
        name,
        parent,
        SUM(value) AS self_value
    FROM TEST
    GROUP BY name, parent
),

第二步：递归CTE遍历树累加总和 #

用递归公共表表达式（CTE）遍历整个树结构，逐层累加子节点的总和到父节点：

-- 递归计算每个节点的自身+所有子节点总和
tree_sum AS (
    -- 锚点成员：初始节点，总和先等于自身value
    SELECT
        name,
        parent,
        self_value AS total_value,
        CAST(name AS VARCHAR(1000)) AS path -- 可选：记录节点路径，用于调试
    FROM node_base
    UNION ALL
    -- 递归成员：关联父节点，累加子节点的总和
    SELECT
        p.name,
        p.parent,
        p.total_value + c.total_value AS total_value,
        CONCAT(p.path, '->', c.name) AS path
    FROM tree_sum p
    JOIN node_base c ON p.name = c.parent
)

第三步：输出最终结果 #

递归过程中每个节点会生成多条中间记录，取每个节点的最大总和即为最终结果：

-- 最终查询：分组取每个节点的最大总和
SELECT
    name,
    parent,
    MAX(total_value) AS value
FROM tree_sum
GROUP BY name, parent
ORDER BY name ASC;

完整SQL代码 #

WITH node_base AS (
    SELECT
        name,
        parent,
        SUM(value) AS self_value
    FROM TEST
    GROUP BY name, parent
),
tree_sum AS (
    SELECT
        name,
        parent,
        self_value AS total_value,
        CAST(name AS VARCHAR(1000)) AS path
    FROM node_base
    UNION ALL
    SELECT
        p.name,
        p.parent,
        p.total_value + c.total_value AS total_value,
        CONCAT(p.path, '->', c.name) AS path
    FROM tree_sum p
    JOIN node_base c ON p.name = c.parent
)
SELECT
    name,
    parent,
    MAX(total_value) AS value
FROM tree_sum
GROUP BY name, parent
ORDER BY name ASC;

逻辑说明 #

1. node_base：解决原始表的重复行问题，得到每个节点的自身Value总和，对应你原来的一级汇总逻辑。
2. tree_sum：通过递归遍历树，把每个父节点的总和不断加上子节点的总和，实现任意深度的累加。
3. 最终分组取最大值：因为递归过程中每个节点会被多次计算（每累加一个子节点就生成一条记录），最大值就是该节点自身+所有子节点的总和。

执行后会得到你期望的结果：

注：该方案支持MySQL 8.0+、PostgreSQL、SQL Server等支持递归CTE的主流数据库。

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