这是一个可行的解决方案.用语言很难描述它为什么起作用，因此我建议拆开查询以了解它如何起作用.基本上，我们使用 ROW_NUMBER 来递归地构建要查看的路径字符串，以跟踪每个新添加的路径属于哪个特定父级.

Here is a working solution. It is difficult to describe in words why this works, so I recommend taking apart the query to see how it works yourself. Basically, we recursively build the path string you want to see, using ROW_NUMBER to keep track to which particular parent each new path addition belongs.

recursiveCte (parentId, id, name, Level, Path, FullPath) AS (
    SELECT d.parentId, d.id, d.name, 0 AS Level,
        CAST(ROW_NUMBER() OVER (ORDER BY d.id) AS nvarchar(max)),
        RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY d.id) AS nvarchar(max)), 3)
    FROM Department AS d
    WHERE parentId = 0 

    UNION ALL

    SELECT d.parentId, d.id, d.name, r.Level + 1,
        r.Path + '.' +
        CAST(ROW_NUMBER() OVER (PARTITION BY r.Level ORDER BY d.id) AS nvarchar(max)),
        r.FullPath + '.' + RIGHT('000' + CAST(ROW_NUMBER() OVER
            (PARTITION BY r.Level ORDER BY d.id) AS nvarchar(max)), 3)
    FROM Department AS d
    INNER JOIN recursiveCte AS r
        ON d.parentId = r.id
)

SELECT parentId, id, name, Level, Path, FullPath
FROM recursiveCte
ORDER BY FullPath;

演示

我稍微修改了原始答案，以便现在使用固定宽度的版本对路径字符串进行排序，即每个数字的固定宽度均为3位数字.这意味着 001 总是会排在 010 之前，这是我们想要的行为.

I slightly edited my original answer so that it now sorts the path string using a fixed-width version, i.e. every number has a fixed width of 3 digits. This means that 001 will always sort before 010, which is the behavior we want.