关系树的存储之嵌套集合模型(Nested set model)

1. 概述

  嵌套集模型(Nested set model) 是由Joe Celko在《Joe Celko’s Trees and Hierarchies in SQL for Smarties》一书中提出，用这种模型表示的树也可以左右值编码树。
  嵌套集模型是根据树遍历对节点进行编号，遍历会访问每个节点两次，并按访问顺序分配编号，并且每次访问时都会分配编号。这样会为每个节点留下两个编号，它们被存储为两个属性。这样查询变得低廉但更新需要重新编号，因此成本很高。
遍历的顺序下图中的红色标号

2. 插入节点

-- 添加节点
declare @parent  VARCHAR(63)='F'; 
declare @parentRight  int;
declare @depthValue  int;

SELECT @parentRight=a.[Right],@depthValue=a.Depth
FROM TreeNestedSet as a
WHERE a.[Name]=@parent

UPDATE TreeNestedSet SET [Right]=[Right]+2 WHERE [Right]>=@parentRight; --先更新左编码
UPDATE TreeNestedSet SET [Left]=[Left]+2 WHERE [Left]>@parentRight;-- 更新左编码
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('K', @parentRight, @parentRight+1, @depthValue+1);-- 插入

3. 删除

declare @name  VARCHAR(63)='R';
declare @leftValue  int;
declare @rightValue  int;

SELECT @leftValue=a.[Left],@rightValue=a.[Right]
FROM TreeNestedSet as a
WHERE a.Name=@name

DELETE FROM TreeNestedSet  WHERE [Left]>=@leftValue AND [Right]<=@rightValue
UPDATE TreeNestedSet SET [Left]=[Left]-(@rightValue-@leftValue+1) WHERE [Left]>@leftValue;
UPDATE TreeNestedSet SET [Right]=[Right]-(@rightValue-@leftValue+1) WHERE [Right]>@rightValue;

4. 左移

declare @current  VARCHAR(63)='F';-- 被移动的元素id
declare @target  VARCHAR(63)='K';-- 目标元素Id
declare @currentLeft  int;
declare @currentRight  int;
declare @targetLeft  int;
declare @targetRight  int;

SELECT @currentLeft=a.[Left],@currentRight=a.[Right]
FROM TreeNestedSet as a
WHERE a.[Name]=@current

SELECT @targetLeft=a.[Left],@targetRight=a.[Right]
FROM TreeNestedSet as a
WHERE a.[Name]=@target

-- 更新涉及到的节点
UPDATE TreeNestedSet SET [Left]=[Left]+(@currentRight-@currentLeft+1) WHERE [Left]>@targetRight AND [Left]<@currentLeft
UPDATE TreeNestedSet SET [Right]=[Right]+(@currentRight-@currentLeft+1) WHERE [Right]>=@targetRight AND [Right]<@currentLeft
-- 更新被移动的节点
UPDATE TreeNestedSet SET [Left]=[Left]-(@currentLeft-@targetRight),[Right]=[Right]-(@currentLeft-@targetRight) WHERE ([Left]>@currentLeft AND [Right]<@currentRight) OR Name=@current;

移动之前

移动之后

5. 右移

declare @current  VARCHAR(63)='F';-- 被移动的元素id
declare @target  VARCHAR(63)='J';-- 目标元素Id
declare @currentLeft  int;
declare @currentRight  int;
declare @targetLeft  int;
declare @targetRight  int;

SELECT @currentLeft=a.[Left],@currentRight=a.[Right]
FROM TreeNestedSet as a
WHERE a.[Name]=@current

SELECT @targetLeft=a.[Left],@targetRight=a.[Right]
FROM TreeNestedSet as a
WHERE a.[Name]=@target

-- 更新涉及到的节点
UPDATE TreeNestedSet SET [Left]=[Left]-(@currentRight-@currentLeft+1) WHERE [Left]>@currentRight AND [Left]<=@targetRight
UPDATE TreeNestedSet SET [Right]=[Right]-(@currentRight-@currentLeft+1) WHERE [Right]>@currentRight AND [Right]<@targetRight
-- 更新被移动的节点
UPDATE TreeNestedSet SET [Left]=[Left]+(@targetRight-@currentRight-1),[Right]=[Right]+(@targetRight-@currentRight-1) WHERE ([Left]>@currentLeft AND [Right]<@currentRight) OR [Name]=@current;

移动之前

移动之后

6. 查询

基于的数据

-- 查询所有的孩子
SELECT a.* 
FROM TreeNestedSet as a
JOIN TreeNestedSet as b on a.[Left]> b.[Left] AND a.[Right]< b.[Right]
WHERE b.Name='D'
ORDER BY a.[Left] ASC

结果：

-- 只查询儿子
select a.* 
from TreeNestedSet a 
inner join TreeNestedSet b on a.[Left] > b.[Left] and a.[Right] < b.[Right] and a.Depth = b.Depth + 1
where b.[Name] = 'D'
ORDER BY a.[Left] ASC

结果：

-- 查询所有叶子节点
SELECT * 
FROM TreeNestedSet as a 
WHERE a.[Right]=a.[Left]+1 
ORDER BY a.[Left] ASC

结果为：

-- 查询所属的路径
select a.* 
from TreeNestedSet a 
inner join TreeNestedSet b on a.[Left] < b.[Left] and a.[Right] > b.[Right] 
where b.Name = 'F'
ORDER BY a.[Left] ASC

结果为：

7.表结构和数据

-- 表结构
CREATE TABLE [dbo].[TreeNestedSet] (
  [Name] varchar(255) NOT NULL primary key,
  [Left] int  NOT NULL,
  [Right] int  NOT NULL,
  [Depth] int  NOT NULL
) 
-- 数据
DELETE FROM TreeNestedSet
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('A', 0, 35, 0);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('B', 1, 4, 1);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('C', 2, 3, 2);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('D', 9, 20, 1);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('E', 10, 11, 2);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('F', 12, 17, 2);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('G', 13, 14, 3);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('H', 15, 16, 3);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('I', 18, 19, 2);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('J', 21, 26, 1);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('K', 5, 8, 1);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('L', 6, 7, 2);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('M', 22, 23, 2);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('N', 24, 25, 2);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('O', 27, 34, 1);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('P', 28, 33, 2);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('Q', 29, 30, 3);
INSERT INTO [TreeNestedSet] ([Name], [Left], [Right], [Depth]) VALUES ('R', 31, 32, 3);

自动绘图脚本：在VISIO中使用脚本创建左右编码树

参考：
Nested set model