MySQL 8 层级数据库节点价值计算及层级汇总查询实现

基础信息 #

• 数据模型：采用邻接表结构存储多层级分类关系
• 业务规则1：仅无下级子节点的叶子节点可持有实际value值
• 业务规则2：每个非叶子节点需要汇总其所有下游分支的叶子节点value总和
• 输出要求：按深度优先树遍历顺序排列，每个父节点的全部子节点遍历完成后，插入对应分支的小计行

现有实现痛点 #

• 已编写的递归CTE仅能完成常规树遍历、路径拼接、层级缩进展示，无法自动生成分支汇总行
• 直接使用GROUP BY WITH ROLLUP适配邻接表结构时，会出现排序逻辑不匹配、汇总范围偏移的问题，输出格式不符合预期

当前基础遍历查询逻辑：

WITH RECURSIVE adjacency_list_path (id, parent_id, lvl, name, path, value) AS (
    SELECT id, parent_id, 0 lvl, name, name as path, adjacency_list.`value`
    FROM adjacency_list
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, (cp.lvl + 1), 
           CONCAT(REPEAT("   " , (cp.lvl + 1)), c.name), 
           CONCAT(cp.path, ' > ', c.name), 
           c.`value`
    FROM adjacency_list_path AS cp
             JOIN adjacency_list AS c
                  ON cp.id = c.parent_id
)
SELECT id, parent_id, lvl, name, path, value
FROM adjacency_list_path
ORDER BY path;

目标输出结构 #

输出集包含两类行：

1. 原始树节点行：携带节点id、父id、层级、缩进名称、完整路径、节点自身值、所属分类标签
2. 分支小计行：在所有下游子节点遍历完成后插入，显示汇总分支名称、分支总value值，与所属父节点保持相同缩进层级

注意：所有行的排序严格遵循深度优先遍历规则，不允许打乱树的层级嵌套顺序。

实现方案 #

整体逻辑拆分为三个独立的CTE步骤，避免直接使用WITH ROLLUP带来的结构错位：

1. 树遍历预处理：递归遍历全量节点，为每个节点生成深度优先排序键、层级缩进名称、路径字符串，同时初始化节点自身的value值
2. 分支汇总计算：自下而上从叶子节点开始累加，计算每个非叶子节点下属全分支的value总和
3. 小计行生成与合并：为每个非叶子节点生成对应的小计行，调整小计行的排序键保证其紧跟在所有子节点之后，最后合并原始节点行与小计行统一输出

参考实现代码 #

WITH RECURSIVE
-- 1. 递归遍历全树，生成排序键、层级、路径
node_traverse AS (
    SELECT 
        id, 
        parent_id, 
        0 AS lvl, 
        name, 
        name AS path, 
        value,
        -- 生成深度优先排序键，保证遍历顺序正确
        CAST(LPAD(id, 10, '0') AS CHAR(1000)) AS sort_key
    FROM adjacency_list
    WHERE parent_id IS NULL

    UNION ALL

    SELECT 
        c.id, 
        c.parent_id, 
        nt.lvl + 1, 
        CONCAT(REPEAT('   ', nt.lvl + 1), c.name),
        CONCAT(nt.path, ' > ', c.name),
        c.value,
        CAST(CONCAT(nt.sort_key, LPAD(c.id, 10, '0')) AS CHAR(1000))
    FROM node_traverse nt
    JOIN adjacency_list c ON nt.id = c.parent_id
),
-- 2. 自下而上计算每个节点的全分支汇总值
node_total AS (
    SELECT 
        id, 
        parent_id, 
        lvl, 
        name, 
        path, 
        value,
        sort_key,
        COALESCE(value, 0) AS branch_total
    FROM node_traverse
    -- 从叶子节点开始汇总
    WHERE id NOT IN (SELECT DISTINCT parent_id FROM adjacency_list WHERE parent_id IS NOT NULL)

    UNION ALL

    SELECT 
        nt.id, 
        nt.parent_id, 
        nt.lvl, 
        nt.name, 
        nt.path, 
        nt.value,
        nt.sort_key,
        COALESCE(nt.value, 0) + nt2.branch_total AS branch_total
    FROM node_traverse nt
    JOIN node_total nt2 ON nt2.parent_id = nt.id
),
-- 3. 生成每个非叶子节点对应的小计行
rollup_line AS (
    SELECT
        NULL AS id,
        parent_id,
        lvl,
        CONCAT(REPEAT('   ', lvl), 'TOTAL ', name) AS name,
        NULL AS path,
        branch_total AS value,
        -- 小计行排序键接在原节点所有子节点之后
        CONCAT(sort_key, '|END') AS sort_key
    FROM node_total
    -- 仅对非叶子节点生成小计行
    WHERE id IN (SELECT DISTINCT parent_id FROM adjacency_list WHERE parent_id IS NOT NULL)
)
-- 合并两类行输出
SELECT id, parent_id, lvl, name, path, value
FROM node_traverse
UNION ALL
SELECT id, parent_id, lvl, name, path, value FROM rollup_line
ORDER BY sort_key;

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