多级层级结构中实现子记录紧随父记录排序的SQL方案

这个问题核心是要实现树形数据的深度优先遍历（DFS）排序，让每个父节点后直接跟随它的所有子节点，子节点再依次带出自己的子节点。你原来的查询因为排序逻辑的限制，没法让第三层级的节点紧跟其父节点，我们可以通过递归CTE（公共表表达式）生成节点的路径字符串，以此来实现正确的排序。

解决方案代码 #

WITH RecursiveItems AS (
    -- 锚点查询：选中所有顶层节点（ParentId=0的条目）
    SELECT 
        Id, 
        ParentId, 
        ChildLevel, 
        UltimateParentId,
        -- 初始化顶层节点的路径为自身Id的字符串形式
        CAST(Id AS VARCHAR(MAX)) AS NodePath
    FROM Items
    WHERE ParentId = 0

    UNION ALL

    -- 递归查询：关联子节点，拼接路径
    SELECT 
        i.Id, 
        i.ParentId, 
        i.ChildLevel, 
        i.UltimateParentId,
        -- 将父节点的路径与当前节点Id拼接，形成完整路径
        ri.NodePath + '.' + CAST(i.Id AS VARCHAR(MAX)) AS NodePath
    FROM Items i
    INNER JOIN RecursiveItems ri 
        ON i.ParentId = ri.Id
)
-- 按生成的路径排序，实现深度优先的层级顺序
SELECT Id, ParentId, ChildLevel, UltimateParentId
FROM RecursiveItems
ORDER BY NodePath;

代码解释 #

1. 递归CTE的锚点部分：先选中所有顶层节点（这里是ParentId=0的条目），并为每个顶层节点生成初始路径（就是节点自身的Id）。
2. 递归部分：通过INNER JOIN关联子节点，将父节点的路径与当前子节点的Id拼接，形成从顶层到当前节点的完整路径（比如父节点3的路径是1.3，它的子节点5的路径就是1.3.5）。
3. 排序逻辑：按生成的NodePath字符串排序，字符串的自然排序正好对应深度优先的遍历顺序——父节点的路径是子节点路径的前缀，所以父节点会排在所有子节点之前，子节点又会排在自己的子节点之前。

为什么原来的查询不生效？ #

你原来的排序逻辑是ORDER BY UltimateParentId, ChildLevel, Num，这里的问题在于：

• ChildLevel会先把所有同层级的节点排在一起，比如所有ChildLevel=2的节点会全部排在ChildLevel=3的节点之前，导致第三层级的节点没法紧跟自己的父节点（父节点属于ChildLevel=2）。
• 而递归路径排序则是完全按照树形的嵌套关系来排序，不会受层级编号的限制，完美实现子节点紧跟父节点的需求。

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