如何在平面关系数据库表中操作分层信息
3.18/5 (29投票s)
本文介绍了一种仅通过一次表扫描即可从扁平关系数据库表中快速检索和展示分层信息的技术。
请注意
文章有许多更新，如果您之前阅读过，请重新阅读。
所有 SQL 语句示例均为 Microsoft SQL Server 特有。分隔符/分隔符（“.”字符）的选择与 Microsoft SQL Server 安装的默认英文排序（Order by）整理有关。本文中的示例仅用于演示目的。我已在以下环境测试了示例：
- Microsoft SQL Server 2019 版本 15.0.4033.1
- 语言：英语（美国）
- 整理：SQL_Latin1_General_CP1_CI_AS)
- Microsoft SQL Server Management Studio (SSMS) 版本 15.0.18330.0
请查看文章末尾的 SQL 查询脚本，用于创建数据库、创建表并按本文示例中的数据顺序填充。
警告（建议；何时使用）
评论中的读者（特别鸣谢 C. Grant Anderson）贡献了很多有用的观点。
我建议您仅在需要获取大量数据且树结构较深（即许多级别/分支和子分支）时使用此算法。我原本认为这一点很明显，但从评论中我意识到需要澄清。核心要点是从 RDBMS（关系数据库管理系统，又名拥有扁平表的数据库）获取大量树形数据，并将其超快速地呈现给用户（UI；用户界面）。
对于普通数量的数据，此技术有点“杀鸡用牛刀”，不太适用。它并非旨在盲目替代任何现有技术，而只是另一种可供选择的技术。
我同意关于为什么要向用户显示数百万个节点数据的争论，这通常是不必要的。然而，我向您展示另一个用例。假设您有一堆类对象需要快速填充到分层树结构中的节点和数据，并将其提供给人工智能或其他算法进行处理，但这些算法需要数据以分层/树状格式呈现，那么此技术就会派上用场。再次强调，我将这作为一个可能性，也许是一个有效的用例。我会收集基于评论的讨论，探讨此技术可能存在的有用之处。
算法历史
2002 年，我在印度孟买的 T.C.S.（塔塔咨询服务公司）工作时，正在为 G.E.（通用电气）项目设置一个 Linux C.O.E.（卓越中心）。这个职位是非计费的。目的是为该客户账户内建立 Linux 的交付能力。我面临的一个挑战是，要建立 Linux 能力，需要购买文档管理软件，以便以易于访问的方式存储文档。文档管理系统将有助于创建、维护和传播知识，供他人学习如何交付和执行基于 Linux 的项目。然而，由于我没有预算，我无法购买任何文档管理软件。因此，我使用免费的 A.S.P.、记事本、IIS Express、SQL Server Express 和 Gimp 创建了一个文档管理网站来存放文档。我创建的第一个系统很简单。主页上显示了父文件夹或类别以及文档。单击文件夹或类别名称或文档会在下一个页面上打开。这很糟糕而且很慢。因此，我花了几个月的时间思考如何做得更好。最终，我提出了这个算法，它是基于 1.10.8 版本的。我用记事本（没有 Visual Studio 许可证的预算）编写了复杂难懂的 A.S.P. 代码，构建了一个功能性的文档管理网站。所有其他 C.O.E. 也开始使用我的网站，因为他们喜欢它，并且都需要一个买不起的文档管理系统。
注意 - 根据印度法律，2002 年软件算法不可获得专利。只有与某些硬件整体相关/绑定/依赖的软件解决方案（软件+硬件系统）才能获得专利。因此，我的这项发明不能由 T.C.S. 或发明人本人拥有。这条注释是给那些担心我创建的此算法所有权的人看的。
引言
关系数据库中的一个简单表是 `Employees` 表。它有一个员工 ID 和一个“报告给” ID，这是一个 `employee` ID。填充树形结构的传统方法是查询根节点，即没有父节点的节点；在这种情况下，`Reports to` 列为 `null`。然后，依次查询该节点的所有子节点，即 `Reports to` ID 等于根员工 ID。一直这样做，直到到达结果为空的叶节点。我将描述一种更好的技术，可以避免重复查询。
如何在关系数据库端表示数据
该技术的一部分是在关系表中创建一个新的键列，其中包含分层信息。示例如下：
表名：`TreeTable`。
| 节点 ID（int identity） | 父节点 ID（int） | 我的键（nvarchar[max]） | 数据（nvarchar[max]） |
1 | | A | Akshay Srinivasan |
2 | | B | Douglas Mitchell |
3 | 1 | A.A | George Yates |
4 | 2 | B.A | Dan Brown |
5 | 3 | A.A.A | Chris Jones |
6 | 4 | B.A.A | Matt Daniels |
7 | 1 | A.B | Andrew Brown |
8 | 3 | A.A.B | Timothy Cook |
9 | 3 | A.A.C | Jane Franklin |
10 | 1 | A.C | Zachary Cage |
11 | 3 | A.A.D | Nancy Carter |
12 | 1 | A.D | Bill Smith |
13 | 3 | A.A.E | Frank Richards |
从这个例子可以看出，数据是随机插入的，因此数据是无序的。但是，通过一个按 `[My Key]` 列排序的基本查询，这些数据将按分层顺序排列。示例如下：
select * from TreeTable order by [My Key]
此 SQL 查询的结果如下：
| 节点 ID | 父节点 ID | 我的键 | Data |
1 | NULL | A | Akshay Srinivasan |
3 | 1 | A.A | George Yates |
5 | 3 | A.A.A | Chris Jones |
8 | 3 | A.A.B | Timothy Cook |
9 | 3 | A.A.C | Jane Franklin |
11 | 3 | A.A.D | Nancy Carter |
13 | 3 | A.A.E | Frank Richards |
7 | 1 | A.B | Andrew Brown |
10 | 1 | A.C | Zachary Cage |
12 | 1 | A.D | Bill Smith |
2 | NULL | B | Douglas Mitchell |
4 | 2 | B.A | Dan Brown |
6 | 4 | B.A.A | Matt Daniels |
这里的符号无关紧要。`[My Key]` 列中的值只需要您在树的该级别使用唯一符号，并且这些符号应按照数据库的排序顺序进行排序。这意味着当您在一个级别中插入时，您应该使用下一个可用的符号，在本例中是下一个字母字符。分隔符也可以是任何符号；我为了方便选择了“.”字符。任何级别的键都组合了它之前的父节点的键，这很容易看出。现在的好处是，这几乎不会给现有的关系数据库带来任何负载，因为排序非常基本，并且所有数据库系统都已高效实现了“Order by”。下一步是客户端如何处理这些信息。
当达到符号范围的末尾时，例如字母 `Z`，您将附加到 `Z` 的下一个符号系列，在本例中是字母 `A`，这将导致 `Z.A`。达到此范围的末尾时，下一个将是 `Z.Z.A.`。
选择分隔符/分隔符（在此简单示例中为“.”字符）时的一个重要考虑因素是，您必须确保“.”字符的排序顺序在符号范围内的任何符号之前。在本例中，“.”的排序在 A 到 Z 的字符符号范围之前。
注意：`[Node ID]` 和 `[Parent ID]` 列仅用于让您有机会了解旧概念如何与 `[My Key]` 列中的新概念进行比较，后者由我的算法引入。这是为什么呢？`[My Key]` 列值中最后一个分隔符/分隔符之后的符号等同于 `[Node ID]`。`[My Key]` 列值中最后一个分隔符/分隔符之前的所有符号和分隔符/分隔符等同于 `[Parent ID]` 列。如果您删除 `[Node ID]` 和 `[Parent ID]` 可选列，您将不得不更改 SQL 查询。
客户端处理
客户端只需遍历每一行，检查当前行的 `[My Key]` 列值是否以上一行的 `[My Key]` 列值开头。如果是，则递归以填充下一级别的树。如果不是，则回退到下一级别，并检查匹配项，直到达到根节点级别并将其添加为根节点。这对客户端来说是一个直接的算法。而且，您可以在几分之一秒内填充 `treeview` 或任何其他类型的分层可视化控件，或者您可能想要填充的任何数据结构。让我们谈谈如何向此树插入或更新数据。插入时，您只需检查父节点的 `[My Key]` 列值，并将其附加到要插入表中的节点的 `[My Key]` 值前面，并附加一个在此树级别中尚未使用的唯一标识符。在我的示例中，这将是下一个最高字母字符。它也可以只是您正在插入的当前记录的标识值。就像这样简单，这始终是唯一的，并且由 SQL Server 等数据库实现。或者 Oracle 中的序列。更新将把 `[My Key]` 列值从旧的父节点 `[My Key]` 列值更改为新节点 `[My Key]` 列值。对于您想使用新标识/序列值来附加到节点的新 `[My Key]` 列值的情况，您还可以使用算法将整数转换为您选择的字符符号范围内的字符符号。
摘要
这项技术允许您仅通过一次到关系数据库的查询，而不是多次查询，来高效地从扁平关系表中呈现分层数据。它比现有技术快得多，并且在任何包含分层信息的关系表中都应该采用它。
我在哪里使用它？
当我于 2002 年发明这项技术时，我正在处理一个具有无限增长潜力的巨大树形结构，我当时所知道的现有方法都无法应对。例如，飞机的零件爆炸、包含财富 500 强公司或拥有大量员工的组织结构图信息的 `employee` 表、或涉及巨大树形的科学工作。对于您希望一次性快速显示整个树的小型树形结构，它也很有用。我已经展示了如何 `insert`、`update`、`delete` 和移动分支，以便您可以开箱即用地使用该系统。但这些只是为了支持，而不是使用该技术的理由。这应该能回答“我在哪里使用它？”这个问题。
如何选择
选择基本信息非常简单，下面是一个示例
select * from TreeTable order by [My Key]
从现在开始，“TreeTable”是表名，`[My Key]` 是包含键的列名，例如“ZZH.JA.ZZZD”。
如果您想选择特定父节点下的所有节点。在本例中，选择父节点 `[My Key]` 列值为 'A' 下的所有节点，则
select * from TreeTable where [My Key] like 'A' + '.%' order by [My Key]
如果您想选择一个节点下的子节点，而无需提取该子节点下的所有节点。以下 SQL 查询示例执行了此操作，用于查找 'A.A.' 节点下的所有子节点。
select [My Key] from TreeTable where [My Key] like 'A.A' + '.%' and
(len('A.A') - len(replace('A.A', '.', ''))) + 1 = (len([My Key]) -
len(replace([My Key], '.', '')))
这本质上是在计算分隔符/分隔符的数量，并查看所选父节点（在本例中为 'A.A.'）的分隔符/分隔符（在本例中为“.”字符）的数量是否等于子节点中分隔符/分隔符的数量。因此，给定 'A.A.' 作为提供的父节点 `[My Key]` 列值，'.' 字符的数量为 `1`。其直接子节点必须以 'A.A.' 开头，并且其 `[My Key]` 列值中不得包含另一个 '.' 字符，这表示更深的子级别。所以直接子节点在其 `[My Key]` 列值中的 '.' 字符计数只能是 `2`。以下是 SQL 查询结果：
| 节点 ID（int identity） | 父节点 ID（int） | 我的键（nvarchar[max]） | 数据（nvarchar[max]） |
5 | 3 | A.A.A | Chris Jones |
8 | 3 | A.A.B | Timothy Cook |
9 | 3 | A.A.C | Jane Franklin |
11 | 3 | A.A.D | Nancy Carter |
13 | 3 | A.A.E | Frank Richards |
如何按其他列对树节点进行排序
您已经向表中插入了数据和键，并且希望在同一级别内按某个其他数据列的字母顺序或数字顺序等进行排序。这是一项非平凡的任务。有多种方法。最简单的方法是使用 select order by 技术将数据提取到代码端。然后，在 `[My Key]` 级别内进行代码排序，并填充您的 `treeview` 控件。您也可以通过查询旧的方式（效率不高但可行）来逐级获取数据，并在该节点级别内按数据列对所有子节点进行排序。要做到这一点，请使用以下查询：
select * from TreeTable order by
replace(iif(charindex('.', reverse([My Key]), 1) > 0,
substring([My Key], 1, len([My Key]) - charindex('.',
reverse([My Key]), 1) + 1), [My Key] + '@'), '.', '~'),
Data
`TreeTable` 是表名，`[My Key]` 是包含键的列名，例如 `ZZH.JA.ZZZD`。Data 是我示例中包含字母数据的列，一个简单的姓名列表。因此，这会搜索最后一个 '.' 分隔符符号，并从 `[My Key]` 值中将其删除，只保留父节点，然后按 Data 排序。因此，在节点内，所有子节点都按字母顺序或数字顺序排序。为了对节点进行排序，'.' 分隔符或符号被替换为 '~' 分隔符或符号。这是因为我为我的符号集选择了大写字母，并且它是默认的英文整理，所以 '~' 的排序顺序在 A-Z 之后。对于没有 '.' 符号的根节点，使用 '@' 符号，因为它在 A-Z 和 `~` 之前排序。以下是 SQL 查询结果：
| 节点 ID（int identity） | 父节点 ID（int） | 我的键（nvarchar[max]） | 数据（nvarchar[max]） |
1 | NULL | A | Akshay Srinivasan |
7 | 1 | A.B | Andrew Brown |
9 | 1 | A.D | Bill Smith |
3 | 1 | A.A | George Yates |
8 | 1 | A.C | Zachary Cage |
5 | 3 | A.A.A | Chris Jones |
13 | 3 | A.A.E | Frank Richards |
11 | 3 | A.A.C | Jane Franklin |
12 | 3 | A.A.D | Nancy Carter |
10 | 3 | A.A.B | Timothy Cook |
2 | NULL | B | Douglas Mitchell |
4 | 2 | B.A | Dan Brown |
6 | 4 | B.A.A | Matt Daniels |
要对单个节点的子节点进行排序，SQL 会更简单
select * from TreeTable where [My Key] like 'A.%' order by
replace(iif(charindex('.', reverse([My Key]), 1) > 0,
substring([My Key], 1, len([My Key]) - charindex('.',
reverse([My Key]), 1) + 1), [My Key] + '@'), '.', '~'),
Data
基本上，您拥有父节点的 `[My Key]`，并且希望正确排序其下的树节点，因此添加了“like”Transact-SQL 关键字，该关键字将选取所有子节点。SQL 查询结果是：
| 节点 ID（int identity） | 父节点 ID（int） | 我的键（nvarchar[max]） | 数据（nvarchar[max]） |
7 | 1 | A.B | Andrew Brown |
9 | 1 | A.D | Bill Smith |
3 | 1 | A.A | George Yates |
8 | 1 | A.C | Zachary Cage |
5 | 3 | A.A.A | Chris Jones |
13 | 3 | A.A.E | Frank Richards |
11 | 3 | A.A.C | Jane Franklin |
12 | 3 | A.A.D | Nancy Carter |
10 | 3 | A.A.B | Timothy Cook |
如何创建新的根节点或子节点
添加新根节点时，您需要找出最后插入的根节点是什么。然后，您将最后一个已创建根节点的最后一个字符递增到符号范围内的下一个符号。在本例中，它是“A”到“Z”。所以，例如，最后一个节点是“B”。那么下一个节点将是“C”。
如果父节点的 `[My Key]` 列值中存在分隔符/分隔符（示例中为“.”），那么我们需要找出哪个直接子节点是最后一个节点。一旦我们找到这个 `[My Key]` 列值，我们就将其最后一个字符递增到符号范围内的下一个符号。在本例中，它是“A”到“Z”。所以，例如，最后一个节点是 'A.A.E.'，那么下一个节点将是 'A.A.F.'。如果最后一个节点是 'A.A. Z'，那么下一个节点是 'A.A.ZA'。
以下存储过程实现了在表中创建新记录（插入）。此存储过程接受您要为其创建新节点的父节点的 `[My Key]` 列值，通过参数 'ParentMyKeyValue' 进行传递。如果此参数为 `null` 或空字符串 ('')，则它将检查并创建一个新的根节点。如果此参数有值，则它将附加到一个 `[My Key]` 列值等于此参数值的父节点。第二个参数 'ChildNodeToCreatesDataValue' 只是示例数据，它是一个 `nvarchar(max)`，本质上是一个 Unicode 字符串。第三个参数 'FirstCharInSymbolRange' 是用于定义 `[My Key]` 列值的符号范围的开始。在示例中，这将是 'A'。第四个参数 'LastCharInSymbolRange' 是符号范围的结束。在示例中，这将是 'Z'。第五个参数 'DelimiterOrSeparatorValue' 是用于定义 `[My Key]` 列值的分隔符/分隔符。在示例中，这将是 '.'。
create procedure uspInsertChildNodeGivenParentNodeMyKey
(
@ParentMyKeyValue nvarchar(max),
@ChildNodeToCreatesDataValue nvarchar(max),
@FirstCharInSymbolRange nvarchar(1),
@LastCharInSymbolRange nvarchar(1),
@DelimiterOrSeparatorValue nvarchar(1)
)
as
begin
declare @IntValueOfLastCharInSymbolRange int;
declare @ParentNodeIDValue int;
declare @LastImmediateChildNodesMyKey nvarchar(max);
declare @IntValueOfLastCharacterOfEndOfLastImmediateChildNodesMyKey int;
declare @NewMyKeyValueForNewChildNode nvarchar(max);
declare @LastRootNodeMyKeyValue nvarchar(max);
declare @IntValueOfLastCharacterOfEndOfLastRootNodeMyKeyValue int;
declare @NewMyKeyValueForNewRootNode nvarchar(max);
set @IntValueOfLastCharInSymbolRange = unicode(@LastCharInSymbolRange)
select @ParentNodeIDValue = [Node ID] from TreeTable where [My Key] = @ParentMyKeyValue
/* Check if the supplied parent key has data and that key exists. */
if (@ParentMyKeyValue is null or len(@ParentMyKeyValue) = 0) or
(@ParentMyKeyValue is not null and len(@ParentMyKeyValue) > 0 and
exists (select [My Key] from TreeTable where [My Key] = @ParentMyKeyValue))
begin
if @ParentMyKeyValue is null or len(@ParentMyKeyValue) = 0
/* Adding a root node as the parent key has no data key value */
begin
/* Get the last value of the root node if any. */
select top 1 @LastRootNodeMyKeyValue = [My Key]
from TreeTable where
[Parent ID] is null
order by [My Key] desc;
if @LastRootNodeMyKeyValue is not null and len(@LastRootNodeMyKeyValue) > 0
/* A root node already exists so got to add/insert this new node after it */
begin
/* Get the Unicode value of the last character */
set @IntValueOfLastCharacterOfEndOfLastRootNodeMyKeyValue =
unicode(substring(reverse(@LastRootNodeMyKeyValue), 1, 1));
if @IntValueOfLastCharacterOfEndOfLastRootNodeMyKeyValue =
@IntValueOfLastCharInSymbolRange
/* Last char is at end of symbol range */
begin
/* Get mew start node key and append start of symbol range symbol */
insert into TreeTable ([Parent ID], [My Key], [Data])
values (NULL, substring(@LastRootNodeMyKeyValue, 1,
len(@LastRootNodeMyKeyValue) - 1) + @LastCharInSymbolRange +
@FirstCharInSymbolRange, @ChildNodeToCreatesDataValue);
end
else
/* Character is NOT at the end of symbol range */
begin
/* Increment char to next symbol in symbol range */
insert into TreeTable ([Parent ID], [My Key], [Data])
values (NULL, substring(@LastRootNodeMyKeyValue, 1,
len(@LastRootNodeMyKeyValue) - 1) +
nchar(@IntValueOfLastCharacterOfEndOfLastRootNodeMyKeyValue + 1),
@ChildNodeToCreatesDataValue);
end
end
else
/* There are no root nodes. */
begin
/* Use first symbol in symbol range */
insert into TreeTable ([Parent ID], [My Key], [Data])
values (NULL, @FirstCharInSymbolRange, @ChildNodeToCreatesDataValue);
end
end
else
/* Destination node is not a root node. */
begin
/* Get key value for last node */
select top 1 @LastImmediateChildNodesMyKey = [My Key]
from TreeTable where
[My Key] like @ParentMyKeyValue + @DelimiterOrSeparatorValue + '%' and
(len(@ParentMyKeyValue) - len(replace(@ParentMyKeyValue,
@DelimiterOrSeparatorValue, ''))) + 1 =
(len([My Key]) - len(replace([My Key], @DelimiterOrSeparatorValue, '')))
order by [My Key] desc;
if @LastImmediateChildNodesMyKey is not null _
and len(@LastImmediateChildNodesMyKey) > 0
/* There are existing child nodes. */
begin
/* Get Unicode value of last char symbol of last node */
set @IntValueOfLastCharacterOfEndOfLastImmediateChildNodesMyKey =
unicode(substring(reverse(@LastImmediateChildNodesMyKey), 1, 1))
if @IntValueOfLastCharacterOfEndOfLastImmediateChildNodesMyKey =
@IntValueOfLastCharInSymbolRange
/* Last char symbol is at end of symbol range */
begin
/* The new node key is created by appending the first symbol
in the symbol range */
set @NewMyKeyValueForNewChildNode = @LastImmediateChildNodesMyKey +
@FirstCharInSymbolRange
end
else
/* Last char symbol is NOT at end of symbol range */
begin
/* The new node key is created by incrementing to
next symbol in the symbol range */
set @NewMyKeyValueForNewChildNode = _
substring(@LastImmediateChildNodesMyKey, 1,
len(@LastImmediateChildNodesMyKey) - 1) +
nchar(@IntValueOfLastCharacterOfEndOfLastImmediateChildNodesMyKey + 1)
end
end
else
/* There are no child nodes */
begin
/* The new node key is created by appending first symbol in the symbol range */
set @NewMyKeyValueForNewChildNode = _
@ParentMyKeyValue + @DelimiterOrSeparatorValue +
@FirstCharInSymbolRange
end
/* Add the new node to the destination node */
insert into TreeTable ([Parent ID], [My Key], [Data])
values (@ParentNodeIDValue, _
@NewMyKeyValueForNewChildNode, @ChildNodeToCreatesDataValue)
end
end
end
GO
为了演示存储过程创建根节点的操作。一个没有父节点的节点。尝试以下 SQL 查询：
exec uspInsertChildNodeGivenParentNodeMyKey @ParentMyKeyValue = null,
@ChildNodeToCreatesDataValue = 'Johnathon Swift',
@FirstCharInSymbolRange = 'A',
@LastCharInSymbolRange ='Z',
@DelimiterOrSeparatorValue = '.'
GO
现在您应该会看到一个名为 'C' 的新根节点，其 `[My Key]` 列值为 'C'，其数据列值设置为 'Johnathon Swift'。
为了演示存储过程创建已提供父节点 `[My Key]` 列值的子节点的操作。尝试以下 SQL 查询：
exec uspInsertChildNodeGivenParentNodeMyKey @ParentMyKeyValue = 'A.A',
@ChildNodeToCreatesDataValue = 'Chantal Jeffreys',
@FirstCharInSymbolRange = 'A',
@LastCharInSymbolRange ='Z',
@DelimiterOrSeparatorValue = '.'
GO
现在您应该看到一个名为 'A.A.' 的节点的子节点。新子节点的 `[My Key]` 列值将是 'A.A.F.'。这是因为最后一个子节点的 `[My Key]` 列值为 'A.A.E.'。数据列值当然被设置为提供的 'Chantal Jeffreys'。
如何删除节点
仅删除一个节点很简单，只需删除该键的节点即可。您永远不应在不删除其子节点的情况下删除一个节点，因为孤立的节点会破坏绘图算法。要删除一个节点及其所有子节点，您将使用以下语句来删除，例如，节点 'A.A.' 的所有子节点：
delete from TreeTable where [My Key] like 'A.A.%' or [My Key] = 'A.A'
这将删除 'A.A.' 节点及其下方所有分支的节点。
更改节点的父节点
要更新或将一个节点移动为另一个现有节点的子节点，您需要按顺序执行以下逻辑步骤：
- 检查您试图移动到的节点是否是正在移动的节点的子节点。如果是子节点，则不要移动该节点。
- 找到要移动的节点的父节点的 `[My Key]` 列值。如果它是根节点，则其 `[My Key]` 值为 `null` 或为空，可以忽略。
- 找到新父节点（如果有）的现有子节点中的下一个符号值。
- 现在，获取新父节点的 `[My Key]` 列值，并附加在步骤 2 中找到的下一个符号。这将是正在移动到新父节点的节点的新的 `[My Key]` 列值。
- 用步骤 3 中的新 `[My Key]` 列值替换步骤 1 中的旧父节点 `[My Key]` 列值。对要移动的节点及其所有子节点执行此操作。
以下是将节点及其所有子节点移动到另一个目标节点的存储过程：
create procedure uspMoveNodeAlongWithItsChildrenUnderANewTargetNode
(
@MyKeyValueOfNodeToBeMoved nvarchar(max),
@MyKeyValueOfNodeToMoveTo nvarchar(max),
@FirstCharInSymbolRange nvarchar(1),
@LastCharInSymbolRange nvarchar(1),
@DelimiterOrSeparatorValue nvarchar(1)
)
as
begin
declare @NodeToBeMovedTosLastImmediateChildMyKeyValue nvarchar(max)
declare @ParentIDOfNodeToMoveTo int
declare @NewMyKey nvarchar(max)
declare @IntValueOfLastCharInSymbolRange int
declare @IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart int
set @IntValueOfLastCharInSymbolRange = unicode(@LastCharInSymbolRange);
select @ParentIDOfNodeToMoveTo = [Node ID] from TreeTable where
[My Key] = @MyKeyValueOfNodeToMoveTo;
/* If a node value is supplied then check to see if it exists.
Do not move a node to one of its children */
if iif(@MyKeyValueOfNodeToBeMoved is not null and len(@MyKeyValueOfNodeToBeMoved) > 0 and
exists (select [My Key] from TreeTable where [My Key] = @MyKeyValueOfNodeToBeMoved),
iif(charindex(@MyKeyValueOfNodeToBeMoved, @MyKeyValueOfNodeToMoveTo) <> 1, iif(
(@MyKeyValueOfNodeToMoveTo is null or len(@MyKeyValueOfNodeToMoveTo) = 0) or (
@MyKeyValueOfNodeToMoveTo is not null and len(@MyKeyValueOfNodeToMoveTo) > 0 and
exists (select [My Key] from TreeTable where [My Key] = @MyKeyValueOfNodeToMoveTo)),
'Enter', 'Exit'), 'Exit'), 'Exit') = 'Enter'
begin
if @MyKeyValueOfNodeToMoveTo is null or len(@MyKeyValueOfNodeToMoveTo) = 0
/* You are moving the node to root. That is no Parent. */
begin
/* Get the last most root node value */
select top 1 @NodeToBeMovedTosLastImmediateChildMyKeyValue = [My Key] _
from TreeTable where
charindex(@DelimiterOrSeparatorValue, [My Key]) = 0 order by [My Key] desc;
/* get the unicode value of the last character of the last most root node key */
set @IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart =
unicode(substring_
(reverse(@NodeToBeMovedTosLastImmediateChildMyKeyValue), 1, 1));
if @IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart = _
@IntValueOfLastCharInSymbolRange
/* the last most node is the last symbol in the symbol range */
begin
/* The new key is the start of the old key with first symbol
in symbol range appended to it */
update TreeTable set [Parent ID] = null, [My Key] =
@NodeToBeMovedTosLastImmediateChildMyKeyValue + _
@FirstCharInSymbolRange where
[My Key] = @MyKeyValueOfNodeToBeMoved;
/* Redo all the childrens key to reflect the value of their
parents movement to root node */
update TreeTable set [My Key] = @NodeToBeMovedTosLastImmediateChildMyKeyValue +
@FirstCharInSymbolRange + @DelimiterOrSeparatorValue + substring([My Key],
len(@MyKeyValueOfNodeToBeMoved) + 2, len([My Key]) -
len(@MyKeyValueOfNodeToBeMoved) - 1) where [My Key] like
@MyKeyValueOfNodeToBeMoved + @DelimiterOrSeparatorValue + '%';
end
else
/* The last most node is NOT the last symbol in the symbol range */
begin
/* Increment the last char value and append it to make the new key */
set @NewMyKey = substring(@NodeToBeMovedTosLastImmediateChildMyKeyValue, 1,
len(@NodeToBeMovedTosLastImmediateChildMyKeyValue) - 1) +
nchar(@IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart + 1);
/* Update the new key value for the node to be moved */
update TreeTable set [Parent ID] = null,
[My Key] = @NewMyKey where [My Key] = @MyKeyValueOfNodeToBeMoved;
/* Update all the children of the node to be moved
with the new key at the start
followed by the rest of the childrens old key */
update TreeTable set [My Key] = @NewMyKey + _
@DelimiterOrSeparatorValue + substring([My Key],
len(@MyKeyValueOfNodeToBeMoved) + 2, _
len([My Key]) - len(@MyKeyValueOfNodeToBeMoved) - 1)
where [My Key] like @MyKeyValueOfNodeToBeMoved + _
@DelimiterOrSeparatorValue + '%';
end
end
else
/* The node being moved has a parent */
begin
/* Get the last immediate child key of the node to be moved to */
select top 1 @NodeToBeMovedTosLastImmediateChildMyKeyValue = _
[My Key] from TreeTable where
[My Key] like @MyKeyValueOfNodeToMoveTo + @DelimiterOrSeparatorValue + '%' and
(len(@MyKeyValueOfNodeToMoveTo) - len(replace(@MyKeyValueOfNodeToMoveTo,
@DelimiterOrSeparatorValue, ''))) + 1 =
(len([My Key]) - len(replace([My Key], @DelimiterOrSeparatorValue, '')))
order by [My Key] desc;
if @NodeToBeMovedTosLastImmediateChildMyKeyValue is null or
len(@NodeToBeMovedTosLastImmediateChildMyKeyValue) = 0
/* The node to be moved to has no children' */
begin
/* Update the Node to be moved [My Key] with the destination new key */
update TreeTable set [Parent ID] = @ParentIDOfNodeToMoveTo,
[My Key] = @MyKeyValueOfNodeToMoveTo + @DelimiterOrSeparatorValue +
@FirstCharInSymbolRange where [My Key] = @MyKeyValueOfNodeToBeMoved;
/* Update all the children of the moved node with the new destination key */
update TreeTable set [My Key] = @MyKeyValueOfNodeToMoveTo + _
@DelimiterOrSeparatorValue
+ @FirstCharInSymbolRange + @DelimiterOrSeparatorValue + substring([My Key],
len(@MyKeyValueOfNodeToBeMoved) + 2, _
len([My Key]) - len(@MyKeyValueOfNodeToBeMoved)
- 1) where [My Key] like @MyKeyValueOfNodeToBeMoved + _
@DelimiterOrSeparatorValue + '%';
end
else
/* The node to be moved to has children */
begin
/* Unicode value of the last char of the last child
of the children of the node to be moved to. */
set @IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart =
unicode(substring(reverse_
(@NodeToBeMovedTosLastImmediateChildMyKeyValue), 1, 1));
if @IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart = _
@IntValueOfLastCharInSymbolRange
/* Last char is the last symbol in the range */
begin
/* Append the first symbol on the range to the key */
set @NewMyKey = @NodeToBeMovedTosLastImmediateChildMyKeyValue + _
@FirstCharInSymbolRange;
update TreeTable set [Parent ID] = @ParentIDOfNodeToMoveTo, [My Key] =
@NewMyKey where [My Key] = @MyKeyValueOfNodeToBeMoved;
/* update all the children of the
node to be moved with the new starting key */
update TreeTable set [My Key] = @NewMyKey + @DelimiterOrSeparatorValue +
substring([My Key], len(@MyKeyValueOfNodeToBeMoved) + 2,
len([My Key]) -
len(@MyKeyValueOfNodeToBeMoved) - 1) where
[My Key] like @MyKeyValueOfNodeToBeMoved + _
@DelimiterOrSeparatorValue + '%';
end
else
/* Last char is NOT the last symbol in the range */
begin
/* Increment the last char value and append it to make the new key */
set @NewMyKey =
substring(@NodeToBeMovedTosLastImmediateChildMyKeyValue, 1,_
len(@NodeToBeMovedTosLastImmediateChildMyKeyValue) - 1) + _
nchar(@IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart_
+ 1);
/* Update the new key value for the node to be moved */
update TreeTable set [Parent ID] = @ParentIDOfNodeToMoveTo,
[My Key] = @NewMyKey where [My Key] = @MyKeyValueOfNodeToBeMoved;
/* Update all the children of the node to be moved with the
new key at the start
followed by the rest of the childrens old key */
update TreeTable set [My Key] = @NewMyKey + @DelimiterOrSeparatorValue +
substring([My Key],
len(@MyKeyValueOfNodeToBeMoved) + 2, len([My Key]) -
len(@MyKeyValueOfNodeToBeMoved) - 1) where [My Key] like
@MyKeyValueOfNodeToBeMoved + @DelimiterOrSeparatorValue + '%';
end
end
end
end
end
GO
我现在将把 `[My Key]` 列值为 'B' 的根节点移动到 `[My Key]` 列值为 'A.A' 的节点。我还将更新该节点的所有子节点的 `[My Key]` 列值以反映此更改，使用以下 SQL 查询：
exec uspMoveNodeAlongWithItsChildrenUnderANewTargetNode
@MyKeyValueOfNodeToBeMoved = 'B',
@MyKeyValueOfNodeToMoveTo = 'A.A',
@FirstCharInSymbolRange = 'A',
@LastCharInSymbolRange = 'Z',
@DelimiterOrSeparatorValue = '.'
GO
Microsoft SQL Server 脚本
以下 Microsoft SQL Server 脚本用于创建数据库、创建表、创建存储过程，并按照本文示例中使用的数据插入顺序填充示例数据。
USE [master]
GO
IF (EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE name = 'TreeDB'))
/****** Object: Database [TreeDB] Script Date: 5/15/2020 7:48:22 AM ******/
DROP DATABASE [TreeDB]
GO
/****** Object: Database [TreeDB] Script Date: 5/15/2020 7:46:00 AM ******/
CREATE DATABASE [TreeDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TreeDB', _
FILENAME = N'D:\SQLServerUserDatabases\TreeDB.mdf' , SIZE = 8192KB , _
MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'TreeDB_log', _
FILENAME = N'D:\SQLServerUserDatabases\TreeDB_log.ldf' , SIZE = 8192KB , _
MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TreeDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [TreeDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TreeDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TreeDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TreeDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TreeDB] SET ARITHABORT OFF
GO
ALTER DATABASE [TreeDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TreeDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TreeDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TreeDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TreeDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TreeDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TreeDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TreeDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TreeDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TreeDB] SET DISABLE_BROKER
GO
ALTER DATABASE [TreeDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TreeDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TreeDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TreeDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TreeDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TreeDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TreeDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [TreeDB] SET RECOVERY FULL
GO
ALTER DATABASE [TreeDB] SET MULTI_USER
GO
ALTER DATABASE [TreeDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TreeDB] SET DB_CHAINING OFF
GO
ALTER DATABASE [TreeDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [TreeDB] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [TreeDB] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [TreeDB] SET QUERY_STORE = OFF
GO
ALTER DATABASE [TreeDB] SET READ_WRITE
GO
USE [TreeDB]
GO
/****** Object: Table [dbo].[TreeTable] Script Date: 5/15/2020 7:40:29 AM ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = _
OBJECT_ID(N'[dbo].[TreeTable]') AND type in (N'U'))
DROP TABLE [dbo].[TreeTable]
GO
/****** Object: Table [dbo].[TreeTable] Script Date: 5/15/2020 7:40:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TreeTable](
[Node ID] [int] IDENTITY(1,1) NOT NULL,
[Parent ID] [int] NULL,
[My Key] [nvarchar](max) NOT NULL,
[Data] [nvarchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[uspInsertChildNodeGivenParentNodeMyKey]
Script Date: 5/15/2020 12:11:44 PM ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = _
OBJECT_ID(N'[dbo].[uspInsertChildNodeGivenParentNodeMyKey]') AND type in (N'P'))
DROP PROCEDURE [dbo].[uspInsertChildNodeGivenParentNodeMyKey]
GO
/****** Object: StoredProcedure [dbo].[uspInsertChildNodeGivenParentNodeMyKey]
Script Date: 5/15/2020 12:11:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure uspInsertChildNodeGivenParentNodeMyKey
(
@ParentMyKeyValue nvarchar(max),
@ChildNodeToCreatesDataValue nvarchar(max),
@FirstCharInSymbolRange nvarchar(1),
@LastCharInSymbolRange nvarchar(1),
@DelimiterOrSeparatorValue nvarchar(1)
)
as
begin
declare @IntValueOfLastCharInSymbolRange int;
declare @ParentNodeIDValue int;
declare @LastImmediateChildNodesMyKey nvarchar(max);
declare @IntValueOfLastCharacterOfEndOfLastImmediateChildNodesMyKey int;
declare @NewMyKeyValueForNewChildNode nvarchar(max);
declare @LastRootNodeMyKeyValue nvarchar(max);
declare @IntValueOfLastCharacterOfEndOfLastRootNodeMyKeyValue int;
declare @NewMyKeyValueForNewRootNode nvarchar(max);
set @IntValueOfLastCharInSymbolRange = unicode(@LastCharInSymbolRange)
select @ParentNodeIDValue = [Node ID] from TreeTable where [My Key] = @ParentMyKeyValue
/* Check if the supplied parent key has data and that key exists. */
if (@ParentMyKeyValue is null or len(@ParentMyKeyValue) = 0) or
(@ParentMyKeyValue is not null and len(@ParentMyKeyValue) > 0 and
exists (select [My Key] from TreeTable where [My Key] = @ParentMyKeyValue))
begin
if @ParentMyKeyValue is null or len(@ParentMyKeyValue) = 0
/* Adding a root node as the parent key has no data key value */
begin
/* Get the last value of the root node if any. */
select top 1 @LastRootNodeMyKeyValue = [My Key]
from TreeTable where
[Parent ID] is null
order by [My Key] desc;
if @LastRootNodeMyKeyValue is not null and len(@LastRootNodeMyKeyValue) > 0
/* A root node already exists so got to add/insert this new node after it */
begin
/* Get the Unicode value of the last character */
set @IntValueOfLastCharacterOfEndOfLastRootNodeMyKeyValue =
unicode(substring(reverse(@LastRootNodeMyKeyValue), 1, 1));
if @IntValueOfLastCharacterOfEndOfLastRootNodeMyKeyValue =
@IntValueOfLastCharInSymbolRange
/* Last char is at end of symbol range */
begin
/* Get mew start node key and append start of symbol range symbol */
insert into TreeTable ([Parent ID], [My Key], [Data])
values (NULL, substring(@LastRootNodeMyKeyValue, 1,
len(@LastRootNodeMyKeyValue) - 1) + @LastCharInSymbolRange +
@FirstCharInSymbolRange, @ChildNodeToCreatesDataValue);
end
else
/* Character is NOT at the end of symbol range */
begin
/* Increment char to next symbol in symbol range */
insert into TreeTable ([Parent ID], [My Key], [Data])
values (NULL, substring(@LastRootNodeMyKeyValue, 1,
len(@LastRootNodeMyKeyValue) - 1) +
nchar(@IntValueOfLastCharacterOfEndOfLastRootNodeMyKeyValue + 1),
@ChildNodeToCreatesDataValue);
end
end
else
/* There are no root nodes. */
begin
/* Use first symbol in symbol range */
insert into TreeTable ([Parent ID], [My Key], [Data])
values (NULL, @FirstCharInSymbolRange, @ChildNodeToCreatesDataValue);
end
end
else
/* Designation node is not a root node. */
begin
/* Get key value for last node */
select top 1 @LastImmediateChildNodesMyKey = [My Key]
from TreeTable where
[My Key] like @ParentMyKeyValue + @DelimiterOrSeparatorValue + '%' and
(len(@ParentMyKeyValue) - len(replace(@ParentMyKeyValue,
@DelimiterOrSeparatorValue, ''))) + 1 =
(len([My Key]) - len(replace([My Key], @DelimiterOrSeparatorValue, '')))
order by [My Key] desc;
if @LastImmediateChildNodesMyKey is not null and _
len(@LastImmediateChildNodesMyKey) > 0
/* There are existing child nodes. */
begin
/* Get Unicode value of last char symbol of last node */
set @IntValueOfLastCharacterOfEndOfLastImmediateChildNodesMyKey =
unicode(substring(reverse(@LastImmediateChildNodesMyKey), 1, 1))
if @IntValueOfLastCharacterOfEndOfLastImmediateChildNodesMyKey =
@IntValueOfLastCharInSymbolRange
/* Last char symbol is at end of symbol range */
begin
/* The new node key is created by appending the
first symbol in the symbol range */
set @NewMyKeyValueForNewChildNode = @LastImmediateChildNodesMyKey +
@FirstCharInSymbolRange
end
else
/* Last char symbol is NOT at end of symbol range */
begin
/* The new node key is created by incrementing
to next symbol in the symbol range */
set @NewMyKeyValueForNewChildNode = _
substring(@LastImmediateChildNodesMyKey, 1, _
len(@LastImmediateChildNodesMyKey) - 1) + _
nchar(@IntValueOfLastCharacterOfEndOfLastImmediateChildNodesMyKey + 1)
end
end
else
/* There are no child nodes */
begin
/* The new node key is created by appending first symbol in the symbol range */
set @NewMyKeyValueForNewChildNode = _
@ParentMyKeyValue + @DelimiterOrSeparatorValue +
@FirstCharInSymbolRange
end
/* Add the new node to the destination node */
insert into TreeTable ([Parent ID], [My Key], [Data])
values (@ParentNodeIDValue, @NewMyKeyValueForNewChildNode, _
@ChildNodeToCreatesDataValue)
end
end
end
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = _
OBJECT_ID(N'[dbo].[uspMoveNodeAlongWithItsChildrenUnderANewTargetNode]') AND type in (N'P'))
DROP PROCEDURE [dbo].[uspMoveNodeAlongWithItsChildrenUnderANewTargetNode]
GO
/****** Object: StoredProcedure [dbo].[uspInsertChildNodeGivenParentNodeMyKey]
Script Date: 5/15/2020 12:11:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure uspMoveNodeAlongWithItsChildrenUnderANewTargetNode
(
@MyKeyValueOfNodeToBeMoved nvarchar(max),
@MyKeyValueOfNodeToMoveTo nvarchar(max),
@FirstCharInSymbolRange nvarchar(1),
@LastCharInSymbolRange nvarchar(1),
@DelimiterOrSeparatorValue nvarchar(1)
)
as
begin
declare @NodeToBeMovedTosLastImmediateChildMyKeyValue nvarchar(max)
declare @ParentIDOfNodeToMoveTo int
declare @NewMyKey nvarchar(max)
declare @IntValueOfLastCharInSymbolRange int
declare @IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart int
set @IntValueOfLastCharInSymbolRange = unicode(@LastCharInSymbolRange);
select @ParentIDOfNodeToMoveTo = [Node ID] from TreeTable where
[My Key] = @MyKeyValueOfNodeToMoveTo;
/* If a node value is supplied then check to see if it exists.
Do not move a node to one of its children */
if iif(@MyKeyValueOfNodeToBeMoved is not null and len(@MyKeyValueOfNodeToBeMoved) > 0 and
exists (select [My Key] from TreeTable where [My Key] = @MyKeyValueOfNodeToBeMoved),
iif(charindex(@MyKeyValueOfNodeToBeMoved, @MyKeyValueOfNodeToMoveTo) <> 1, iif(
(@MyKeyValueOfNodeToMoveTo is null or len(@MyKeyValueOfNodeToMoveTo) = 0) or (
@MyKeyValueOfNodeToMoveTo is not null and len(@MyKeyValueOfNodeToMoveTo) > 0 and
exists (select [My Key] from TreeTable where [My Key] = @MyKeyValueOfNodeToMoveTo)),
'Enter', 'Exit'), 'Exit'), 'Exit') = 'Enter'
begin
if @MyKeyValueOfNodeToMoveTo is null or len(@MyKeyValueOfNodeToMoveTo) = 0
/* You are moving the node to root. That is no Parent. */
begin
/* Get the last most root node value */
select top 1 @NodeToBeMovedTosLastImmediateChildMyKeyValue = _
[My Key] from TreeTable where
charindex(@DelimiterOrSeparatorValue, [My Key]) = 0 order by [My Key] desc;
/* get the unicode value of the last character of the last most root node key */
set @IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart =
unicode(substring(reverse
(@NodeToBeMovedTosLastImmediateChildMyKeyValue), 1, 1));
if @IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart = _
@IntValueOfLastCharInSymbolRange
/* the last most node is the last symbol in the symbol range */
begin
/* The new key is the start of the old key with first symbol
in symbol range appended to it */
update TreeTable set [Parent ID] = null, [My Key] =
@NodeToBeMovedTosLastImmediateChildMyKeyValue + _
@FirstCharInSymbolRange where
[My Key] = @MyKeyValueOfNodeToBeMoved;
/* Redo all the childrens key to reflect the value
of their parents movement to root node */
update TreeTable set [My Key] =
@NodeToBeMovedTosLastImmediateChildMyKeyValue + _
@FirstCharInSymbolRange + _
@DelimiterOrSeparatorValue + substring([My Key], _
len(@MyKeyValueOfNodeToBeMoved) + 2, len([My Key]) -
len(@MyKeyValueOfNodeToBeMoved) - 1) where [My Key] like
@MyKeyValueOfNodeToBeMoved + @DelimiterOrSeparatorValue + '%';
end
else
/* The last most node is NOT the last symbol in the symbol range */
begin
/* Increment the last char value and append it to make the new key */
set @NewMyKey = substring(@NodeToBeMovedTosLastImmediateChildMyKeyValue, 1, _
len(@NodeToBeMovedTosLastImmediateChildMyKeyValue) - 1) + _
nchar(@IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart + 1);
/* Update the new key value for the node to be moved */
update TreeTable set [Parent ID] = null,
[My Key] = @NewMyKey where [My Key] = @MyKeyValueOfNodeToBeMoved;
/* Update all the children of the node to be moved
with the new key at the start
followed by the rest of the childrens old key */
update TreeTable set [My Key] = @NewMyKey + _
@DelimiterOrSeparatorValue + substring([My Key],
len(@MyKeyValueOfNodeToBeMoved) + 2, len([My Key]) - _
len(@MyKeyValueOfNodeToBeMoved) - 1)
where [My Key] like @MyKeyValueOfNodeToBeMoved + _
@DelimiterOrSeparatorValue + '%';
end
end
else
/* The node being moved has a parent */
begin
/* Get the last immediate child key of the node to be moved to */
select top 1 @NodeToBeMovedTosLastImmediateChildMyKeyValue = _
[My Key] from TreeTable where _
[My Key] like @MyKeyValueOfNodeToMoveTo + _
@DelimiterOrSeparatorValue + '%' and _
(len(@MyKeyValueOfNodeToMoveTo) - len(replace(@MyKeyValueOfNodeToMoveTo, _
@DelimiterOrSeparatorValue, ''))) + 1 = _
(len([My Key]) - len(replace([My Key], @DelimiterOrSeparatorValue, '')))_
order by [My Key] desc;
if @NodeToBeMovedTosLastImmediateChildMyKeyValue is null or
len(@NodeToBeMovedTosLastImmediateChildMyKeyValue) = 0
/* The node to be moved to has no children' */
begin
/* Update the Node to be moved [My Key] with the destination new key */
update TreeTable set [Parent ID] = @ParentIDOfNodeToMoveTo,
[My Key] = @MyKeyValueOfNodeToMoveTo + @DelimiterOrSeparatorValue +
@FirstCharInSymbolRange where [My Key] = @MyKeyValueOfNodeToBeMoved;
/* Update all the children of the moved node with the new destination key */
update TreeTable set [My Key] = @MyKeyValueOfNodeToMoveTo + _
@DelimiterOrSeparatorValue
+ @FirstCharInSymbolRange + _
@DelimiterOrSeparatorValue + substring([My Key], _
len(@MyKeyValueOfNodeToBeMoved) + 2, len([My Key]) - _
len(@MyKeyValueOfNodeToBeMoved)
- 1) where [My Key] like @MyKeyValueOfNodeToBeMoved + _
@DelimiterOrSeparatorValue + '%';
end
else
/* The node to be moved to has children */
begin
/* Unicode value of the last char of the last child of the
children of the node to be moved to. */
set @IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart =
unicode(substring(reverse(@NodeToBeMovedTosLastImmediateChildMyKeyValue),_
1, 1));
if @IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart = _
@IntValueOfLastCharInSymbolRange
/* Last char is the last symbol in the range */
begin
/* Append the first symbol on the range to the key */
set @NewMyKey = @NodeToBeMovedTosLastImmediateChildMyKeyValue + _
@FirstCharInSymbolRange;
update TreeTable set [Parent ID] = @ParentIDOfNodeToMoveTo, [My Key] =
@NewMyKey where [My Key] = @MyKeyValueOfNodeToBeMoved;
/* update all the children of the node to be moved
with the new starting key */
update TreeTable set [My Key] = @NewMyKey + @DelimiterOrSeparatorValue +
substring([My Key], len(@MyKeyValueOfNodeToBeMoved) + 2, _
len([My Key]) -
len(@MyKeyValueOfNodeToBeMoved) - 1) where
[My Key] like @MyKeyValueOfNodeToBeMoved + _
@DelimiterOrSeparatorValue + '%';
end
else
/* Last char is NOT the last symbol in the range */
begin
/* Increment the last char value and append it to make the new key */
set @NewMyKey = _
substring(@NodeToBeMovedTosLastImmediateChildMyKeyValue, 1,_
len(@NodeToBeMovedTosLastImmediateChildMyKeyValue) - 1) + _
nchar(@IntValueOfNodeToBeMovedTosLastImmediateChildMyKeyValueLastPart_
+ 1);
/* Update the new key value for the node to be moved */
update TreeTable set [Parent ID] = @ParentIDOfNodeToMoveTo,
[My Key] = @NewMyKey where [My Key] = @MyKeyValueOfNodeToBeMoved;
/* Update all the children of the node to be moved with the
new key at the start
followed by the rest of the childrens old key */
update TreeTable set [My Key] = @NewMyKey + @DelimiterOrSeparatorValue +
substring([My Key], _
len(@MyKeyValueOfNodeToBeMoved) + 2, len([My Key]) -
len(@MyKeyValueOfNodeToBeMoved) - 1) where [My Key] like
@MyKeyValueOfNodeToBeMoved + @DelimiterOrSeparatorValue + '%';
end
end
end
end
end
GO
insert into TreeTable ([Parent ID], [My Key], [Data]) values(NULL, 'A', 'Akshay Srinivasan');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(NULL, 'B', 'Douglas Mitchell');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(1, 'A.A', 'George Yates');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(2, 'B.A', 'Dan Brown');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(3, 'A.A.A', 'Chris Jones');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(4, 'B.A.A', 'Matt Daniels');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(1, 'A.B', 'Andrew Brown');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(3, 'A.A.B', 'Timothy Cook');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(3, 'A.A.C', 'Jane Franklin');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(1, 'A.C', 'Zachary Cage');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(3, 'A.A.D', 'Nancy Carter');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(1, 'A.D', 'Bill Smith');
insert into TreeTable ([Parent ID], [My Key], [Data]) values(3, 'A.A.E', 'Frank Richards');
历史
- 2006 年 10 月 23 日：初始版本
- 2020 年 5 月 16 日：重大更新