多级Parent Child表递归查询：如何实现层级遍历排序？

问题背景 #

现有一张N级父子层级结构的表，执行以下SQL查询：

SELECT pcn.id,
       pcn.config_id,
       pcnc.nombre_nivel,
       pcnc.orden_nivel,
       pcn.nivel_padre_id,
       pcnc.empresa_id,
       pcnc.proyecto_id,
       pcnc.activo AS activo_config,
       pcnc.usuario_id,
       pcn.activo
FROM puebles_ciclos_niveles pcn
JOIN puebles_ciclos_niveles_config pcnc ON pcn.config_id = pcnc.id

得到的结果未按层级排序，需要实现**父节点→子节点1→子节点2→子节点3…**的深度优先遍历排序（无后续子节点时返回上一层继续处理）。此前尝试的递归CTE仅能处理一层层级，无法得到正确排序结果。

解决方案：带路径字段的递归CTE #

通过在递归CTE中维护节点的层级路径，实现深度优先排序：

WITH RecursiveCTE AS (
    SELECT
        pcn.id, 
        pcn.config_id, 
        pcnc.nombre_nivel, 
        pcnc.orden_nivel,
        pcn.nivel_padre_id,
        pcnc.empresa_id, 
        pcnc.proyecto_id, 
        pcnc.activo AS activo_config, 
        pcnc.usuario_id,
        pcn.activo,
        -- 初始化路径：根节点ID转为字符串
        CAST(pcn.id AS VARCHAR(MAX)) AS node_path
    FROM
        puebles_ciclos_niveles pcn
    JOIN
        puebles_ciclos_niveles_config pcnc ON pcn.config_id = pcnc.id
    WHERE
        pcn.nivel_padre_id = 0 -- 选取根节点

    UNION ALL

    SELECT
        pcn.id, 
        pcn.config_id, 
        pcnc.nombre_nivel, 
        pcnc.orden_nivel,
        pcn.nivel_padre_id,
        pcnc.empresa_id, 
        pcnc.proyecto_id, 
        pcnc.activo AS activo_config, 
        pcnc.usuario_id,
        pcn.activo,
        -- 递归拼接路径：父节点路径 + 当前节点ID
        rc.node_path + ',' + CAST(pcn.id AS VARCHAR(MAX)) AS node_path
    FROM
        puebles_ciclos_niveles pcn
    JOIN
        puebles_ciclos_niveles_config pcnc ON pcn.config_id = pcnc.id
    JOIN
        RecursiveCTE rc ON pcn.nivel_padre_id = rc.id
)

SELECT
    id, 
    config_id, 
    nombre_nivel, 
    orden_nivel,
    nivel_padre_id,
    empresa_id, 
    proyecto_id, 
    activo_config, 
    usuario_id,
    activo
FROM RecursiveCTE
ORDER BY node_path; -- 按层级路径排序实现深度优先遍历

代码说明 #

1. node_path字段：记录从根节点到当前节点的ID路径（例如根节点ID为1，其子节点ID为2，子节点的子节点ID为3，则路径为1,2,3）。
2. 递归拼接路径：在递归部分，将父节点的路径与当前节点ID拼接，形成完整的层级路径。
3. 按路径排序：最终查询时通过ORDER BY node_path，让数据库按照路径字符串的顺序排序，从而实现父节点优先、子节点依次跟进的深度优先遍历顺序。

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