构建多级树状结构的MS SQL Server数据库解决方案

数据库设计中,树状结构是一种常见的数据组织方式,它能够清晰地表示层次关系。本文将介绍一种在MS SQL Server数据库中构建和管理多级树状结构的有效方法。这种方法不仅简单易用,而且经过了MS SQL Server 2005/2008的全面测试。

将通过一个名为“索引字典”的列值来展示和管理树状结构。通过查看或排序这个值,可以直观地理解树的结构。

本文将展示一种简单的工作方式,可以处理任何树级,这里以示例树级=3为例。当需要一个树状数据结构时,可以使用这个解决方案,它将为节省时间。安装这个解决方案后,不需要学习CTE查询或其他复杂的构建树的方法。只需选择所有行并按“索引字典”列排序,树就构建好了!非常简单。

这个解决方案使用序数、字母和罗马数字来标记树的级别:

  • 大写字母如A、B用于级别0 - 根级别。
  • 罗马字母用于级别1。
  • 数字用于级别2。

可以下载SQL脚本或完整的数据库恢复文件。

目的和设计

目标是创建一个多级树,并使用多种类型的序列号来索引行,如序数(1, 2, 3...)、字母(A, B, C...)或罗马数字(I, II, III...)。结果可能如下所示:

图1

为了部署这个解决方案,将在MS SQL Server中创建一个树数据表,使用以下结构:

图2

Tree_ID是自动增长的序列。它被称为树节点ID。

Parent_ID在Tree_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,或者恢复完整的数据库

第一组:函数

  • [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。

第二组:编辑树的SP

[insert_tree_node]:插入新树节点的SP,同时重新计算目录 [remove_node]:删除树节点的SP,同时重新计算目录 [move_node_up]:向上移动树节点的SP,同时重新计算目录 [move_node_down]:向下移动树节点的SP,同时重新计算目录

第三组:查看结果

  • [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

现在,在运行代码块之后,将通过运行查询再次检查树:

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节点中的位置顺序排列节点。

这个解决方案将提供一个视觉上的树输出。可以轻松理解、查看树结构,并通过查看输出结果来计算树级别。

沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485