不使用hierarchyid，通过递归CTE实现深度优先层级排序

问题根源是字符串排序按字符逐个比较，比如"100"和"2"，第一个字符'1'比'2'小，导致"100"排在"2"前面，不符合数值排序预期。我们可以通过将每个order1转换为固定长度的补零字符串解决这个问题，确保数值大的order1对应字符串也更大，无需依赖复杂函数或hierarchyid。

修正后的实现代码 #

假设order1最大值不超过9999（可根据实际数据调整长度），用RIGHT('0000' + CAST(order1 AS VARCHAR), 4)把每个order1补成4位长度字符串，再拼接成排序路径：

WITH RecursiveCTE AS (
    -- 锚点成员：根节点（根据实际数据调整判断条件，比如p_Id为NULL或0）
    SELECT 
        Id,
        p_Id,
        order1,
        RIGHT('0000' + CAST(order1 AS VARCHAR), 4) AS sort_path,
        1 AS level
    FROM #t1
    WHERE p_Id IS NULL

    UNION ALL

    -- 递归成员：关联子节点
    SELECT 
        child.Id,
        child.p_Id,
        child.order1,
        parent.sort_path + '.' + RIGHT('0000' + CAST(child.order1 AS VARCHAR), 4) AS sort_path,
        parent.level + 1 AS level
    FROM #t1 child
    INNER JOIN RecursiveCTE parent ON child.p_Id = parent.Id
)
-- 按补零后的路径排序，得到正确的深度优先结果
SELECT 
    Id,
    p_Id,
    order1,
    level,
    sort_path
FROM RecursiveCTE
ORDER BY sort_path;

关键说明 #

• 固定长度补零：把order1转换成等长字符串，比如1→"0001"、2→"0002"、100→"0100"，让字符串排序逻辑和数值排序完全一致。
• 路径拼接：递归时拼接父节点的排序路径与当前节点的补零order1，生成的sort_path可直接用于深度优先排序。
• 灵活调整：如果order1最大值更大，只需调整补零的长度（比如改成'00000'对应5位）即可，无需修改核心逻辑。

示例验证 #

假设#t1有以下数据：

修正后的sort_path分别为：

• 1: "0001"
• 2: "0001.0002"
• 4: "0001.0002.0001"
• 3: "0001.0100"

最终排序顺序为1→2→4→3，完全符合深度优先且order1数值升序的预期。

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