我希望能够以“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
感谢您的选择。