MySQL递归查询实现树形转矩阵：分支计数问题

环境与需求 #

使用Delphi 11.3、TADOQuery和MySQL 8，将treestructure表中的树形数据转换为矩阵格式的报表。表结构及数据如下：

矩阵的行对应树形的独立分支，最终预期矩阵结果：

问题：递归CTE中分支号计算错误 #

原尝试的递归CTE无法正确计算Branch字段，预期的递归中间结果应为：

解决方案：基于叶子节点回溯的分支计数 #

1. 正确计算分支号的递归CTE #

通过三步实现分支号的正确分配：

• 递归获取所有节点的路径、层级及是否为叶子节点
• 给叶子节点按路径顺序分配分支号（每个叶子对应一条独立分支）
• 从叶子节点回溯，将分支号传递给所有父节点

完整SQL如下：

WITH RECURSIVE tree_nodes AS (
    SELECT 
        Node,
        Parent,
        Data,
        CAST(Node AS CHAR(255)) AS Path,
        1 AS Level,
        NOT EXISTS (SELECT 1 FROM treestructure t2 WHERE t2.Parent = t.Node) AS IsLeaf
    FROM treestructure t
    WHERE Parent IS NULL
    UNION ALL
    SELECT 
        t2.Node,
        t2.Parent,
        t2.Data,
        CONCAT(tn.Path, ',', t2.Node) AS Path,
        tn.Level + 1 AS Level,
        NOT EXISTS (SELECT 1 FROM treestructure t3 WHERE t3.Parent = t2.Node) AS IsLeaf
    FROM treestructure t2
    INNER JOIN tree_nodes tn ON t2.Parent = tn.Node
),
leaf_branches AS (
    SELECT 
        Path,
        Node,
        ROW_NUMBER() OVER (ORDER BY Path) AS Branch
    FROM tree_nodes
    WHERE IsLeaf = 1
),
branch_assignment AS (
    SELECT 
        tn.Node,
        tn.Parent,
        tn.Path,
        tn.Level,
        tn.Data,
        lb.Branch
    FROM tree_nodes tn
    INNER JOIN leaf_branches lb ON tn.Path LIKE CONCAT(lb.Path, '%') OR tn.Path = lb.Path
    UNION ALL
    SELECT 
        tn.Node,
        tn.Parent,
        tn.Path,
        tn.Level,
        tn.Data,
        ba.Branch
    FROM tree_nodes tn
    INNER JOIN branch_assignment ba ON tn.Node = ba.Parent
    WHERE tn.Path NOT IN (SELECT Path FROM branch_assignment)
)
SELECT DISTINCT
    Path,
    Branch,
    Level,
    Node,
    Parent,
    Data
FROM branch_assignment
ORDER BY Path;

2. 直接生成矩阵报表的SQL #

在分支号的基础上，使用条件聚合将层级数据转换为矩阵列：

WITH RECURSIVE tree_nodes AS (
    SELECT 
        Node,
        Parent,
        Data,
        CAST(Node AS CHAR(255)) AS Path,
        1 AS Level,
        NOT EXISTS (SELECT 1 FROM treestructure t2 WHERE t2.Parent = t.Node) AS IsLeaf
    FROM treestructure t
    WHERE Parent IS NULL
    UNION ALL
    SELECT 
        t2.Node,
        t2.Parent,
        t2.Data,
        CONCAT(tn.Path, ',', t2.Node) AS Path,
        tn.Level + 1 AS Level,
        NOT EXISTS (SELECT 1 FROM treestructure t3 WHERE t3.Parent = t2.Node) AS IsLeaf
    FROM treestructure t2
    INNER JOIN tree_nodes tn ON t2.Parent = tn.Node
),
leaf_branches AS (
    SELECT 
        Path,
        Node,
        ROW_NUMBER() OVER (ORDER BY Path) AS Branch
    FROM tree_nodes
    WHERE IsLeaf = 1
),
branch_assignment AS (
    SELECT 
        tn.Node,
        tn.Parent,
        tn.Path,
        tn.Level,
        tn.Data,
        lb.Branch
    FROM tree_nodes tn
    INNER JOIN leaf_branches lb ON tn.Path LIKE CONCAT(lb.Path, '%') OR tn.Path = lb.Path
    UNION ALL
    SELECT 
        tn.Node,
        tn.Parent,
        tn.Path,
        tn.Level,
        tn.Data,
        ba.Branch
    FROM tree_nodes tn
    INNER JOIN branch_assignment ba ON tn.Node = ba.Parent
    WHERE tn.Path NOT IN (SELECT Path FROM branch_assignment)
),
unique_branches AS (
    SELECT DISTINCT
        Branch,
        Level,
        Data
    FROM branch_assignment
)
SELECT
    Branch,
    MAX(CASE WHEN Level = 1 THEN Data ELSE '' END) AS c1,
    MAX(CASE WHEN Level = 2 THEN Data ELSE '' END) AS c2,
    MAX(CASE WHEN Level = 3 THEN Data ELSE '' END) AS c3,
    MAX(CASE WHEN Level = 4 THEN Data ELSE '' END) AS c4
FROM unique_branches
GROUP BY Branch
ORDER BY Branch;

3. Delphi中使用TADOQuery实现 #

• 创建TADOQuery组件，关联已配置好的MySQL ADO连接。
• 将上述矩阵SQL赋值给ADOQuery.SQL.Text。
• 调用ADOQuery.Open()加载数据，可直接绑定到TDBGrid或报表组件进行展示。

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