Sql SELECT 用于 Parent、Children 和 ORDER BY [Order] 列

我在SQL Server中有一个表myTable，如下所示:

我希望我的存储过程返回给定ParentId的输出，其中所有Id及其子对象按Order排序.

上表所需结果的示例:

测试ParentId=1应返回:

测试ParentId=4应返回--Testing that even when a 101 is not a 100 of it self the lineage in correct order is produced

测试ParentId=2应返回:

测试ParentId=3应返回:

测试ParentId=10应该返回(这是一个棘手的问题):

我用以下代码成功地获得了给定ParentId的Id，并能够递归地对Id的Id进行排序:

DECLARE @myTable TABLE (Id INT, ParentID INT, [Order] INT)

INSERT INTO @myTable 
VALUES
(1,1,1),
(2,2,2),
(3,3,3),
(4,1,2),
(5,1,1),
(6,1,3),
(7,4,1),
(8,2,2),
(9,2,1),
(100,3,1),
(10,10,1),
(194,10,1),
(295,194,1),
(205,194,2),
(322,10,2),
(215,194,3),
(404,10,3),
(435,10,4),
(507,10,5),
(206,10,6),
(330,10,7),
(425,10,8),
(428,10,9),
(488,10,10),
(432,10,11),
(633,10,12)


IF OBJECT_ID(N'tempdb..#myTable') IS NOT NULL
BEGIN
    DROP TABLE #myTable
END

SELECT * 
INTO #myTable 
FROM @myTable;

WITH ChildHierarchy AS 
(
    -- Base query: find the direct children of the given ParentId
    SELECT 
        Id, ParentId, [Order], 0 AS HierarchyLevel, 
        CAST([Order] AS VARCHAR(MAX)) AS SortPath
    FROM 
        #myTable
    WHERE 
        ParentId = 1 AND Id != 1

    UNION ALL

    -- Recursive query: find the children of each child
    SELECT 
        O.Id, O.ParentId, O.[Order], HierarchyLevel + 1, 
        CAST(ChildHierarchy.SortPath + '.' + CAST(O.[Order] AS VARCHAR(MAX)) AS VARCHAR(MAX))
    FROM 
        #myTable O
    INNER JOIN 
        ChildHierarchy ON O.ParentId = ChildHierarchy.Id
)
SELECT * 
FROM 
    (SELECT 
         Id, ParentId, [Order], CAST(0 AS varchar) AS HierarchyLevel, 
         CAST(0 AS varchar) AS SortPath 
     FROM 
         #myTable 
     WHERE 
         Id = 1

     UNION
     
     SELECT * 
         FROM ChildHierarchy) AS X
     ORDER BY 
    [Order] ASC, X.SortPath ASC

All the test produce the write order except Test ParentId = 10 gives:

在@MyTable中，每行只有一个层次 struct 级别(即没有父代的子代将位于另一列的行上)

感谢你们在这方面的任何帮助.