如何通过MariaDB查询实现层级数据的结构化展示？

我需要在MariaDB 10.4.32中查询并展示层级数据（比如食谱列表、企业组织架构、程序层级），体现数据间的层级关系，必要时可配合PHP处理。之前在DB2里能用CONNECT_BY_ROOT和CONNECT BY PRIOR语法实现这类关系展示，但MySQL/MariaDB不支持该语法。

示例 #

输入数据 #

Parent Child
    A        D
    B        E
    B        G
    E        H 

期望输出 #

1.     A
1.1      D
  
2.     B
2.1      E
2.1.1      H
2.2      G

当前实现（通用表表达式CTE） #

目前我用CTE实现了基础的层级查询，但还无法生成带编号的层级结构，代码如下：

WITH RECURSIVE hierarchy_cte AS (
    -- 锚点成员：从根节点开始
    SELECT WHPNAM  as parent,
           WHFNAM  as child
    FROM rf_pgm_ref
    WHERE WHPNAM = 'ADDRESSR'  -- 指定起始根节点

    UNION ALL

    SELECT 
           h.WHPNAM    as parent,
           h.WHFNAM    as child
        FROM rf_pgm_ref h
    INNER JOIN hierarchy_cte cte ON h.WHPNAM = cte.child
)
SELECT DISTINCT   parent, child
FROM hierarchy_cte
ORDER BY parent, child;

改进方案：生成带层级编号的结果 #

要实现示例中的带编号层级展示，可以在CTE中追踪层级路径、深度，同时为每个节点生成序号：

WITH RECURSIVE hierarchy_cte AS (
    -- 锚点成员：初始化根节点，记录层级编号、深度和路径
    SELECT 
        WHPNAM AS parent,
        WHFNAM AS child,
        CAST(ROW_NUMBER() OVER (ORDER BY WHFNAM) AS CHAR(100)) AS level_num,
        1 AS depth
    FROM rf_pgm_ref
    WHERE WHPNAM = 'ADDRESSR'  -- 起始根节点

    UNION ALL

    SELECT 
        h.WHPNAM AS parent,
        h.WHFNAM AS child,
        CONCAT(cte.level_num, '.', ROW_NUMBER() OVER (PARTITION BY cte.child ORDER BY h.WHFNAM)) AS level_num,
        cte.depth + 1 AS depth
    FROM rf_pgm_ref h
    INNER JOIN hierarchy_cte cte ON h.WHPNAM = cte.child
)
-- 按层级编号排序，输出结构化结果
SELECT 
    CONCAT(level_num, '     ', child) AS hierarchical_output
FROM hierarchy_cte
ORDER BY level_num;

配合PHP优化格式 #

如果需要更贴合示例的缩进和换行格式，可以用PHP处理查询结果：

// 假设已通过PDO连接数据库
$sql = "WITH RECURSIVE hierarchy_cte AS (...)"; // 填入上述完整CTE语句
$stmt = $pdo->query($sql);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($results as $row) {
    $levelNum = $row['level_num'];
    $child = $row['child'];
    // 根据层级编号的点数生成缩进
    $indent = str_repeat('      ', substr_count($levelNum, '.'));
    echo $levelNum . '     ' . $indent . $child . "\n\n";
}

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