SQL Server递归查询按Order列排序父子层级数据问题求助

你的问题出在递归CTE没有构建全局排序路径，单独按[Order]排序只能保证当前层级的顺序，跨层级时会错乱。下面是修正后的存储过程，通过拼接父-子的[Order]为排序路径，实现整个层级树的正确排序：

CREATE PROCEDURE GetHierarchicalItems
    @ParentId INT
AS
BEGIN
    SET NOCOUNT ON;

    WITH HierarchyCTE AS (
        -- 锚点：获取指定ParentId的直接子项，初始化排序路径
        SELECT 
            Id,
            ParentId,
            [Order],
            -- 格式化Order为固定长度字符串，避免数字排序问题（如10和2的排序错误）
            CAST(RIGHT('0000' + CAST([Order] AS VARCHAR(4)), 4) AS VARCHAR(MAX)) AS SortPath
        FROM myTable
        WHERE ParentId = @ParentId

        UNION ALL

        -- 递归：获取子项，拼接排序路径
        SELECT 
            child.Id,
            child.ParentId,
            child.[Order],
            CAST(parent.SortPath + RIGHT('0000' + CAST(child.[Order] AS VARCHAR(4)), 4) AS VARCHAR(MAX)) AS SortPath
        FROM myTable child
        INNER JOIN HierarchyCTE parent ON child.ParentId = parent.Id
    )

    -- 按全局排序路径输出结果
    SELECT 
        Id,
        ParentId,
        [Order]
    FROM HierarchyCTE
    ORDER BY SortPath;
END

关键说明： #

• SortPath字段：将每一层的[Order]格式化为4位固定长度字符串（可根据你的[Order]最大值调整长度），然后拼接成完整的路径。比如父级Order=2，子级Order=10，SortPath会是00020010，这样排序时能保证父级顺序在前，子级按自身Order排列。
• 若需要展示层级深度，可以在CTE中添加Level字段（锚点设为1，递归时加1），方便查看层级结构。

测试验证： #

假设你的myTable有如下数据：

执行EXEC GetHierarchicalItems @ParentId=10，返回结果会按以下顺序排列：

1. Id=12（ParentId=10，Order=1）
2. Id=15（ParentId=12，Order=1）
3. Id=14（ParentId=12，Order=2）
4. Id=11（ParentId=10，Order=2）
5. Id=13（ParentId=11，Order=1）

完全符合[Order]的层级排序要求。

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