在 MS SQL Server 中实现多级树
4.86/5 (15投票s)
一种使用 MS SQL Server 数据库设置和安装多级树结构的良好方法。
引言
现在许多项目都需要管理多级树形数据。有一些方法可以做到这一点，但在本文中，我将介绍一种使用 MS SQL Server 数据库设置和安装多级树结构的良好方法。我们的结果已在 MS SQL Server 2005/2008 中得到充分测试。
树将通过一个列的值来显示和管理，我称之为“索引字典”术语。通过查看此值或对其进行排序，我们可以理解树的结构。
我的文章将向您展示一种处理树数据的简单方法。该解决方案可以处理您想要的任何树级别，我将使用一个示例树级别 = 3。
当您有需要树数据结构的需求时，可以使用此解决方案，它将为您节省时间。安装此解决方案后，您无需再学习 CTE 查询或其他复杂方法来构建树。只需选择所有行并按“索引字典”列排序，树就构建好了！非常简单。
该解决方案使用序数、字母和罗马数字来标记树级别
- 大写字母，如 A、B，用于级别 0 - 根级别。
- 罗马字母用于级别 1
- 数字用于级别 2
您可以下载 SQL 脚本或完整的数据库恢复包（在 zip 示例文件中）。
目的和设计
我们的目的是创建一个多级树，并使用多种顺序编号，如序数（1、2、3...）、字母（A、B、C...）或罗马数字（I、II、III...）来索引行。结果可能显示为
要部署此解决方案，我们将在 MS SQL Server 中创建一个树数据表，使用此结构
Tree_ID 是自增序列。它被称为树的 NodeID。当 Tree_ID 是树的根时，Parent_ID 为 null 或 0。当 Tree_ID 是子节点时，Parent_ID 是父节点的 Tree_ID。Seq_Index 是子节点序号的指示器，有效值仅为单个索引：A、B、C 或 1、2、3... Full_index 显示从根到子节点的完整路径字典，例如：A.I.1、A.II.1、B.I.2、B.I.3...
使用代码
我们将创建一个函数和一个存储过程（SP），并将它们分为三组。您可以在脚本文件或恢复完整数据库中找到所有这些函数和 SP。
第 1 组：函数
[count_tree_level]：用于检查树节点级别的函数。[get_seq_by_level]：用于根据树级别计算seq_index的函数。[count_tree_full_index]：用于根据tree_id计算full_index的函数。[get_reverse_ascii]：用于将序数转换为 ASCII 的函数，例如 1->'A'，2->'B'。使用此函数，您可以将序数转换为大写或小写字符；这可以通过传递ascii_pattern参数来完成。[convert_integer_to_roma]：用于将序数转换为罗马数字的函数（此函数我在网上找到的）。[check_parent]：如果Node_id是选定的parent_ID的子节点或孙节点，则返回 1 的函数。
第 2 组：编辑树的 SP
[insert_tree_node][remove_node]：用于删除树节点的 SP，同时重新计算目录。[move_node_up]：用于向上移动树节点的 SP，同时重新计算目录。[move_node_down]：用于向下移动树节点的 SP，同时重新计算目录。
第 3 组：查看结果
[view_tree]：用于按索引目录顺序查看树的 SP。[view_human_tree]：用于按索引目录顺序查看树的 SP，它对人类来说是直观的，与图 1 相同。
代码示例
以下操作将演示如何使用代码实现多级树结构。首先，清空 DataTreeTbl 中的所有数据。此代码将插入三个名为“Tree A”、“Tree B”和“Tree C”的根节点。
go
exec dbo.insert_tree_node 'Tree A',0
go
exec dbo.insert_tree_node 'Tree B',0
go
exec dbo.insert_tree_node 'Tree C',0
go go
从现在开始，在运行代码块后，我们将通过运行查询再次检查树。
select * from dbo.TreeDataTbl order by Full_index asc
运行该代码后的结果
请记住节点 ID，我们将为上述节点添加子节点。
首先，我们将向根节点添加一个一级子数据（Tree_id 值取决于您在运行上述代码块后获得的实际 Tree_ID）。
go
exec dbo.insert_tree_node 'Tree A.I',73
go
exec dbo.insert_tree_node 'Tree A.II',73
go
exec dbo.insert_tree_node 'Tree B.I',74
go
exec dbo.insert_tree_node 'Tree B.II',74
go
exec dbo.insert_tree_node 'Tree C.I',75
go
结果是：
现在，我们将为上面的树添加一个二级子节点。
go
exec dbo.insert_tree_node 'Tree A.I.1',76
go
exec dbo.insert_tree_node 'Tree A.I.2',76
go
exec dbo.insert_tree_node 'Tree A.I.3',76
go
exec dbo.insert_tree_node 'Tree A.II.1',77
go
exec dbo.insert_tree_node 'Tree B.I.1',78
go
exec dbo.insert_tree_node 'Tree B.I.2',78
go
exec dbo.insert_tree_node 'Tree C.I.1',80
go
exec dbo.insert_tree_node 'Tree C.I.2',80
go
exec dbo.insert_tree_node 'Tree C.I.3',80
go
结果是：
现在我们将编辑此树。首先，我们将节点 82 向上移动。让我们运行此代码。
exec dbo.move_node_up 82
结果是节点 ID=82 移动到了节点 ID=81 的上方。并且 full_Index 已重新计算！
您可以移动任何节点的上下。这次我们将向下移动一个根节点树 Tree_ID=74！
exec dbo.move_node_down 74
结果是，“Tree B”中的所有节点都出现在“Tree C”之后，并且索引目录（Full_index）也已重新计算。
现在我们将从树中删除一个节点。我将尝试删除根节点“Tree C” - Tree_ID=75。
exec dbo.remove_node 75
所有节点 C 及其关系都被删除。“Tree B”被移到前面，我们重新计算了 seq_Index 和 Full_index。
最后，我们将使用两个存储过程来查看树。运行 SP [view_tree] 时的结果
运行 SP [view_human_tree] 时的结果
结论
正如我上面提到的，我们可以使用 CTE 查询来生成树。让我们使用这个 CTE 查询。
WITH Tree_CTE(Tree_ID, Tree_name, Parent_ID, Seq_index, Full_index, Tree_level)
AS
(
SELECT TreeDataTbl.*, 0 FROM TreeDataTbl WHERE Parent_ID =0
UNION ALL
SELECT ChildNode.*, Tree_level+1 FROM TreeDataTbl AS ChildNode
INNER JOIN Tree_CTE
ON ChildNode.Parent_ID = Tree_CTE.Tree_ID
)
SELECT * FROM Tree_CTE order by Tree_level
树形结果是
您可以看到 CTE 方法可以计算树级别，但它无法像本解决方案那样做到。所有行的级别都按顺序显示。此外，CTE 本身无法对同一级别的行数据进行排序，您只能使用字段进行排序。但是，使用此解决方案，您可以轻松地在一个父 ID 节点中对节点位置进行排序。
此解决方案将提供直观的树形输出。您可以轻松理解、查看树结构，并通过查看输出结果来计算树级别。
关注点
正如您在示例图片中看到的，此解决方案可能超过三个级别深。如果您想更改树级别的 seq_index，请在 [get_seq_by_level] 函数中进行自定义。使用罗马数字存在一些树的问题：I、II、III、IV、V、VI、VII、VIII、IX、X... 罗马数字中的 9 (IX) 会出现在罗马数字中的 5 (V) 之前。您可以找到一种简单的方法来解决这个问题！该解决方案可以应用于许多情况。应用程序的多级菜单或报告导出...
希望本文能在您的项目需要安装多级树数据时为您节省时间。