这里是目录哦!
预排序遍历树算法
基本概念
预排序遍历树算法(modified preorder tree traversal),又称左右树,是树状结构的一种数据结构形式。
理解:
1.节点包含左值、右值字段,作为子节点的范围;
2.节点的左值小于所有子节点左值,右值大于所有子节点右值;
特点:
1.无层级限制;
2.查询效率高;
3.修改效率较低;
应用场景:
主要用于查询使用率大于修改的情况,例如:功能菜单等
相关操作示例
悟
① 创建测试数据
-- mssql环境,创建测试树表
CREATE TABLE tree_test(
id INT NOT NULL PRIMARY KEY,
l INT NOT NULL,
r INT NOT NULL,
node_level INT NOT NULL,
tree_node VARCHAR(128) NOT NULL
);
② 查询
1. 根据子节点查询父节点
-- 父节点左值小于子节点左值,右值大于子节点右值,节点等级比子节点小1
SELECT p.* FROM tree_test p
WHERE EXISTS(SELECT * FROM tree_test s
WHERE p.l < s.l AND s.r < p.r AND s.node_level - 1 = p.node_level);
2. 根据父节点查询所有子节点
-- 同上
SELECT * FROM tree_test s
WHERE EXISTS(SELECT * FROM tree_test p
WHERE p.l < s.l AND s.r < p.r AND s.node_level = p.node_level - 1);
③ 新增
1. 插入根节点
-- 直接插入根节点数据
INSERT INTO tree_test(id, l, r,node_level, tree_node)
VALUES(1,0,1,1,'根节点');
2. 在节点左或右插入节点
-- 获取基准节点相关信息
DECLARE @l INT,@r INT,@node_level INT;
SELECT @l = l , @r = r,@node_level = node_level FROM tree_test
WHERE id = 1;
-- 左边插入
UPDATE tree_test
SET l = l + 2
WHERE l >= @l
UPDATE tree_test
SET r = r + 2
WHERE r > @l
INSERT INTO tree_test(id, l, r, node_level, tree_node)
VALUES((SELECT ISNULL(MAX(id),0)+1 FROM tree_test),
@l,@r,@node_level,'');
-- 右边插入
UPDATE tree_test
SET l = l + 2
WHERE l > @r
UPDATE tree_test
SET r = r + 2
WHERE r > @r
INSERT INTO tree_test(id, l, r, node_level, tree_node)
VALUES((SELECT ISNULL(MAX(id),0)+1 FROM tree_test),
@l+2,@r+2,@node_level,'');
3.根据父节点创建子节点
-- 获取父节点相关信息
DECLARE @l INT,@r INT,@node_level INT;
SELECT @l = l , @r = r,@node_level = node_level FROM tree_test
WHERE id = 1;
UPDATE tree_test
SET l = l + 2
WHERE l > @r
UPDATE tree_test
SET r = r + 2
WHERE r >= @r
INSERT INTO tree_test(id, l, r, node_level, tree_node)
VALUES((SELECT ISNULL(MAX(id),0)+1 FROM tree_test),
@r,@r+1,@node_level+1,'');
④ 删除(略)
总结
便于查询的一种数据结构,可以在合适的场景得到很好应用,维护较为麻烦,在负责的应用场景中应注意其CURD的方式。