转自:http://blog.csdn.net/comiunknown/archive/2007/04/26/1586020.aspx
该设计方案的优点是:只用一条查询语句即可得到某个根节点及其所有子孙节点的先序遍历。由于消除了递归,在数据记录量较大时,可以大大提高列表效率。但是,这种编码方案由于层信息位数的限制,限制了每层能所允许的最大子节点数量及最大层数。同时,在添加新节点的时候必须先计算新节点的位置是否超过最大限制。
上面的设计方案必须预先设定类别树的最大层数以及最大子节点数,不是无限分级,在某些场合并不能采用,那么还有更完美的解决方案吗?通过 google的搜索,我又探索到一种全新的无递归查询,无限分级的编码方案——左右值。原文的程序代码是用php写的,但是通过仔细阅读其数据库表设计说明及相关的sql语句,我彻底弄懂了这种巧妙的设计思路,并在这种设计中新增了删除节点,同层平移的需求(原文只提供了列表及插入子节点的sql语句)。
下面我力图用比较简短的文字,少量图表,及相关核心sql语句来描述这种设计方案:
首先,我们弄一棵树作为例子:
商品
|---食品
| |---肉类
| | |--猪肉
| |---蔬菜类
| |--白菜
|---电器
|--电视机
|--电冰箱
Type_id
|
Name
|
Lft
|
Rgt
|
1
|
商品
|
1
|
18
|
2
|
食品
|
2
|
11
|
3
|
肉类
|
3
|
6
|
4
|
猪肉
|
4
|
5
|
5
|
蔬菜类
|
7
|
10
|
6
|
白菜
|
8
|
9
|
7
|
电器
|
12
|
17
|
8
|
电视机
|
13
|
14
|
9
|
电冰箱
|
15
|
16
|
+-----------------+ +---------------------+
3肉类6 7蔬菜类10 13电视机14 15电冰箱16
4猪肉5 8白菜9
Type_id
|
Name
|
Lft
|
Rgt
|
2
|
食品
|
2
|
11
|
3
|
肉类
|
3
|
6
|
4
|
猪肉
|
4
|
5
|
5
|
蔬菜类
|
7
|
10
|
6
|
白菜
|
8
|
9
|
(
@type_id int
)
RETURNS int
AS
begin
declare @result int
set @result = 0
declare @lft int
declare @rgt int
if exists ( select 1 from tree where type_id = @type_id )
begin
select @lft = lft, @rgt = rgt from tree where type_id = @type_id
select @result = count ( * ) from tree where lft <= @lft and rgt >= @rgt
end
return @result
end
GO
AS
SELECT type_id, name, lft, rgt, dbo.CountLayer(type_id) AS layer FROM dbo.tree ORDER BY lft
GO
(
@type_id int -- 给定节点标识
)
AS
declare @lft int
declare @rgt int
if exists ( select 1 from tree where type_id = @type_id )
begin
select @lft = lft, @rgt = rgt from tree where type_id = @type_id
select * from TreeView where lft between @lft and @rgt order by lft asc
end
go
Type_id
|
Name
|
Lft
|
Rgt
|
Layer
|
2
|
食品
|
2
|
11
|
2
|
3
|
肉类
|
3
|
6
|
3
|
4
|
猪肉
|
4
|
5
|
4
|
5
|
蔬菜类
|
7
|
10
|
3
|
6
|
白菜
|
8
|
9
|
4
|
+-----------------+ +-------------------------+
3肉类6+2 7+2蔬菜类10+2 13+2电视机14+2 15+2电冰箱16+2
(
@type_id int ,
@name varchar ( 50 )
)
AS
declare @rgt int
if exists ( select 1 from tree where type_id = @type_id )
begin
SET XACT_ABORT ON
BEGIN TRANSACTION
select @rgt = rgt from tree where type_id = @type_id
update tree set rgt = rgt + 2 where rgt >= @rgt
update tree set lft = lft + 2 where lft >= @rgt
insert into tree (name,lft,rgt) values ( @name , @rgt , @rgt + 1 )
COMMIT TRANSACTION
SET XACT_ABORT OFF
end
go
+-----------------+
3肉类8 9蔬菜类12 17-2电冰箱18-2
+----------+
@type_id int
AS
declare @lft int
declare @rgt int
if exists ( select 1 from tree where type_id = @type_id )
begin
SET XACT_ABORT ON
BEGIN TRANSACTION
select @lft = lft, @rgt = rgt from tree where type_id = @type_id
delete from tree where lft >= @lft and rgt <= @rgt
update tree set lft = lft - ( @rgt - @lft + 1 ) where lft > @lft
update tree set rgt = rgt - ( @rgt - @lft + 1 ) where rgt > @rgt
COMMIT TRANSACTION
SET XACT_ABORT OFF
End
+----------------------+
15-12电冰箱16-12 3+4肉类8+4 9+4蔬菜类12+4
+-------------------+
4+4猪肉5+4 6+4牛肉7+4 10+4白菜11+4
@type_id int
AS
declare @lft int
declare @rgt int
declare @layer int
if exists ( select 1 from tree where type_id = @type_id )
begin
SET XACT_ABORT ON
BEGIN TRANSACTION
select @lft = lft, @rgt = rgt, @layer = layer from TreeView where type_id = @type_id
if exists ( select * from TreeView where rgt = @lft - 1 and layer = @layer )
begin
declare @brother_lft int
declare @brother_rgt int
select @brother_lft = lft, @brother_rgt = rgt from TreeView where rgt = @lft - 1 and layer = @layer
update tree set lft = lft - ( @brother_rgt - @brother_lft + 1 ) where lft >= @lft and rgt <= @rgt
update tree set lft = lft + ( @rgt - @lft + 1 ) where lft >= @brother_lft and rgt <= @brother_rgt
update tree set rgt = rgt - ( @brother_rgt - @brother_lft + 1 ) where rgt > @brother_rgt and rgt <= @rgt
update tree set rgt = rgt + ( @rgt - @lft + 1 ) where lft >= @brother_lft + ( @rgt - @lft + 1 ) and rgt <= @brother_rgt
end
COMMIT TRANSACTION
SET XACT_ABORT OFF
end
缺点:由于这种左右值编码的方式和常见的阿拉伯数字直观排序不同,再加上节点在树中的层次,顺序不是直观显示出来,而必须通过简单的公式计算后得到,需要花费一定的时间对其数学模型进行深入理解。而且,采用该方案编写相关存储过程,新增,删除,同层平移节点需要对整个树进行查询修改,由此导致的代码复杂度,耦合度较高,修改维护的风险较高。