使用递归CTE获取SQL Server路径

Question

我希望能够以“1.1”、“1.2”等格式获得每个部门的路径。这是我的部门表格：

id  name    parentId
--------------------
1   Dep 1   0
2   Dep 2   1
3   Dep 3   0
4   Dep 4   1
5   Dep 5   4
6   Dep 6   2

这是我的递归公用表达式，它可以从根部门开始，在一个扁平的表格中给出父母和孩子。

WITH recursiveCte (parentId, id, name, Level)
AS
(
    -- Anchor member definition
    SELECT 
        d.parentId, d.id, d.name, 
        0 AS Level
    FROM
        Department AS d
    WHERE 
        parentId = 0 

    UNION ALL

    -- Recursive member definition
    SELECT 
        d.parentId, d.id, d.name,
        Level + 1
    FROM 
        Department AS d
    INNER JOIN 
        recursiveCte AS r ON d.parentId = r.id
)
-- Statement that executes the CTE
SELECT parentId,id, name, Level
FROM recursiveCte 
ORDER BY id

当前结果：

parentId    id  name    Level
-------------------------------
0           1   Dep 1   0
1           2   Dep 2   1
0           3   Dep 3   0
1           4   Dep 4   1
4           5   Dep 5   2
2           6   Dep 6   2

期望的结果：

parentId    id  name    Level   Path
--------------------------------------
0           1   Dep 1   0       1  
1           2   Dep 2   1       1.1 
2           6   Dep 6   2       1.1.1
1           4   Dep 4   1       1.2
4           5   Dep 5   2       1.2.1 
0           3   Dep 3   0       2 

感谢您的选择。

- lory

Answer 1

这里提供一个可行的解决方案。很难用言语描述为什么它有效，因此建议拆开查询以查看其工作原理。基本上，我们使用ROW_NUMBER来跟踪每个新路径添加属于哪个特定的父级，递归构建您想要查看的路径字符串。

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之前，这是我们想要的行为。

- Tim Biegeleisen

---

我会稍微修改一下代码，因为路径10会在路径2之前，因为它是一个字符串。 - lory

@lory 我更新了我的答案，现在它使用固定宽度版本的路径字符串来进行排序。 在这种情况下，我假设您不会有任何大于999的路径元素，但您可以根据需要轻松修改此值。 - Tim Biegeleisen