SQL Server使用递归CTE获取路径 [英] SQL Server get path with recursive CTE

问题描述

我想以1.1、1.2等格式获取每个部门的路径.这是我的部门表:

I want to get the path for each department with this format 1.1, 1.2 and so on. This is my department table :

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

这是我的递归CTE，它使我的父母和孩子从根部门开始在平坦的桌子上.

This is my recursive CTE that give me the parents and children in a flat table starting from a root department.

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 

谢谢.

推荐答案

这是一个可行的解决方案.用语言很难描述它为什么起作用，因此我建议拆开查询以了解它如何起作用.基本上，我们使用 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.

这篇关于SQL Server使用递归CTE获取路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！