获取树形数据的全路径

我们经常要用到树形数据，比如：部门、文章栏目、行政区划……，而且有时还需要用到这些数据的全路径，比如：四川省 > 遂宁市 > 船山区。
为了避免每次要使用全路径时都拼接一次，我们可以在数据库增加一个字段来保存全路径，并且使用触发器来生成全路径。
典型的表结构如下：

 由于新增的节点都处于末级，没有子节点，只需要修改新增节点的全路径，如下：

1. ALTER TRIGGER [dbo].[trDeptInsert]
2.     ON [dbo].[tbDept]
3.     AFTER INSERT
4. AS
5. SET NOCOUNT ON
6. --连接inserted表得到新增的数据
7. --连接tbDept表取得上级节点路径，如果是顶层，就没有上级节点，所以需要LEFT JOIN
8. --如果是顶层，全路径就是节点名称，否则，就是上级节点全路径+当前节点名称
9. UPDATE A
10. SET Path = CASE WHEN A.ParentID = 0 THEN A.Title ELSE B.Path + ' > ' + A.Title END
11. FROM tbDept A INNER JOIN inserted I ON A.ID = I.ID LEFT JOIN tbDept B ON A.ParentID = B.ID

复制代码

修改节点后，要做判断，只有修改了节点名称或者所属上级节点，才需要修改全路径，同时，也要修改所有子节点的全路径，如下：

1. ALTER TRIGGER [dbo].[trDeptUpdate]
2.     ON [dbo].[tbDept]
3.     AFTER UPDATE
4. AS
5. SET NOCOUNT ON;
6. --递归查出修改了的节点以及他们的所有后代节点
7. --连接inserted和deleted表，并使用I.ParentID <> D.ParentID OR I.Title <> D.Title判断是否修改了名称和所属父节点
8. WITH temp AS (
9.     SELECT A.ID FROM tbDept A INNER JOIN inserted I ON A.ID = I.ID INNER JOIN deleted D ON I.ID = D.ID
10.     WHERE I.ParentID <> D.ParentID OR I.Title <> D.Title
11.     UNION ALL
12.     SELECT tbDept.ID FROM temp INNER JOIN tbDept ON temp.ID = tbDept.ParentID
13. )
14. --CTE数据只能使用一次，所以需要写入临时表，同时增加行号，以便遍历
15. SELECT ID, RowID = ROW_NUMBER() OVER(ORDER BY ID) INTO #TEMP FROM temp
16. --遍历上面得到的所有节点ID
17. DECLARE @RowCount int = (SELECT COUNT(*) FROM #TEMP), @RowID int = 1    --行数，行号
18. WHILE @RowCount >= @RowID
19. BEGIN
20.     DECLARE @ID int
21.     SELECT @ID = ID FROM #TEMP WHERE RowID = @RowID;
22.     --递归得到当前节点及其所有父节点名称
23.     WITH temp AS (
24.         SELECT Title, ParentID, Level = 0 FROM tbDept WHERE ID = @ID
25.         UNION ALL
26.         SELECT A.Title, A.ParentID, Level - 1 FROM tbDept A INNER JOIN temp ON A.ID = temp.ParentID
27.     )
28.     --使用FOR XML PATH拼接出节点全路径
29.     --使用STUFF函数去掉前面多余的连接符号
30.     --注意使用TYPE和.value('.','NVARCHAR(MAX)')，否则尖括号会被转义，并且value只能小写
31.     UPDATE tbDept
32.     SET Path = STUFF((SELECT ' > '+Title FROM temp ORDER BY Level FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,3,'')
33.     WHERE ID = @ID
34.     SET @RowID = @RowID + 1
35. END

复制代码

数据示例如下：
 
 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！