如何在SQL Server中按父节点匹配规则实现层级排序？

要实现顶级节点（ParentId='#'）优先，且每个顶级节点的所有层级子节点紧跟其后的排序需求，最直接的方式是使用递归CTE生成节点的完整层级路径，通过路径排序实现预期的树形展示。

完整SQL代码 #

WITH TreeHierarchy AS (
    -- 锚点查询：获取所有顶级父节点，初始化排序路径
    SELECT 
        Id,
        ParentId,
        Name,
        CAST(Id AS VARCHAR(MAX)) AS SortPath,
        1 AS NodeLevel
    FROM #Result
    WHERE ParentId = '#'
    
    UNION ALL
    
    -- 递归查询：遍历所有子节点，拼接父节点的排序路径
    SELECT 
        r.Id,
        r.ParentId,
        r.Name,
        CAST(th.SortPath + '/' + r.Id AS VARCHAR(MAX)) AS SortPath,
        th.NodeLevel + 1 AS NodeLevel
    FROM #Result r
    INNER JOIN TreeHierarchy th 
        ON r.ParentId = th.Id
)
SELECT 
    39000 AS WFBLZ,
    Name,
    Id,
    ParentId
FROM TreeHierarchy
ORDER BY SortPath;

代码说明 #

1. 锚点成员：先筛选出所有ParentId='#'的顶级节点，为每个节点生成初始的SortPath（即节点自身的Id），并标记层级为1。
2. 递归成员：通过关联子节点的ParentId和父节点的Id，逐层遍历所有子节点，将父节点的SortPath与当前节点Id拼接，形成完整的层级路径（比如L01_1/J13_4/R45_3），同时层级递增。
3. 排序逻辑：最终通过SortPath排序，确保顶级节点先出现，其所有子节点（包括多层级）会紧跟在父节点之后，完全符合你需要的树形排序规则。

原有方案无效原因 #

你之前的临时表方案仅为顶级节点和直接子节点分配了相同的numbering，但无法区分子节点的层级关系，导致孙节点无法紧跟在其父节点之后，而是和同级的平级节点混排。递归CTE则能完整维护每个节点的层级路径，从根本上解决了多层级排序的问题。

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