TREE采用左右值编码来存储无限分级树形结构的数据库

采用左右值编码来存储无限分级树形结构的数据库表设计之前我介绍过一种按位数编码保存树形结构数据的表设计方法，详情见：浅谈数据库设计技巧(上)该设计方案的优点是：只用一条查询语句即可得到某个根节点及其所有子孙节点的先序遍历。

由于消除了递归，在数据记录量较大时，可以大大提高列表效率。

但是，这种编码方案由于层信息位数的限制，限制了每层能所允许的最大子节点数量及最大层数。

同时，在添加新节点的时候必须先计算新节点的位置是否超过最大限制。

上面的设计方案必须预先设定类别树的最大层数以及最大子节点数，不是无限分级，在某些场合并不能采用，那么还有更完美的解决方案吗？通过google的搜索，我又探索到一种全新的无递归查询，无限分级的编码方案——左右值。

原文的程序代码是用php写的，但是通过仔细阅读其数据库表设计说明及相关的sql语句，我彻底弄懂了这种巧妙的设计思路，并在这种设计中新增了删除节点，同层平移的需求（原文只提供了列表及插入子节点的sql语句）。

下面我力图用比较简短的文字，少量图表，及相关核心sql语句来描述这种设计方案：首先，我们弄一棵树作为例子：商品|---食品| |---肉类| | |--猪肉| |---蔬菜类| |--白菜|---电器|--电视机|--电冰箱select count(*) from tree where lft <= 2 and rgt >= 11为了方便列表，我们可以为tree表建立一个视图，添加一个层数列，该类别的层数可以写一个自定义函数来计算。

该函数如下：CREATE FUNCTION dbo.CountLayer(@type_id int)RETURNS intASbegindeclare@result intset@result=0declare@lft intdeclare@rgt intif exists (select1from tree where type_id=@type_id)beginselect@lft=lft,@rgt=rgt from tree where type_id=@type_idselect@result=count(*) from tree where lft <=@lft and rgt >=@rgtendreturn@resultendGO然后，我们建立如下视图：CREATE VIEW dbo.TreeViewASSELECT type_id, name, lft, rgt, dbo.CountLayer(type_id) AS layer FROM dbo.tree ORDE R BY lftGO()AS declare declare ifgo假定我们要在节点“肉类”下添加一个子节点“牛肉”，该树将变成：1商品18+2+--------------------------------------------+2食品11+2 12+2电器17+2+-----------------+ +-------------------------+3肉类6+2 7+2蔬菜类10+2 13+2电视机14+2 15+2电冰箱16+2 +-------------+4猪肉5 6牛肉78+2白菜9+2看完上图相应节点左右值的变化后，相信大家都知道该如何写相应的sql脚本吧？下面我给出相对完整的插入子节点的存储过程：CREATE PROCEDURE[dbo].[AddSubNodeByNode](@type_id int,@name varchar(50))ASdeclare@rgt intif exists (select1from tree where type_id=@type_id)beginSET XACT_ABORT ONBEGIN TRANSACTIONselect@rgt=rgt from tree where type_id=@type_idupdate tree set rgt=rgt+2where rgt>=@rgtupdate tree set lft=lft+2where lft>=@rgtinsert into tree (name,lft,rgt) values (@name,@rgt,@rgt+1)COMMIT TRANSACTIONSET XACT_ABORT OFFend然后，我们删除节点“电视机”，再来看看该树会变成什么情况：1商品20-2+-----------------------------------+2食品13 14电器19-2+-----------------+3肉类8 9蔬菜类12 17-2电冰箱18-2+----------+4猪肉5 6牛肉7 10白菜11相应的存储过程如下：CREATE PROCEDURE[dbo].[DelNode]@type_id intASdeclare@lft intdeclare@rgt intif exists (select1from tree where type_id=@type_id)beginSET XACT_ABORT ONBEGIN TRANSACTIONselect@lft=lft,@rgt=rgt from tree where type_id=@type_iddelete from tree where lft>=@lft and rgt<=@rgtupdate tree set lft=lft-(@rgt-@lft+1) where lft>@lftupdate tree set rgt=rgt-(@rgt-@lft+1) where rgt>@rgtCOMMIT TRANSACTIONSET XACT_ABORT OFFEnd注意：因为删除某个节点会同时删除该节点的所有子孙节点，而这些被删除的节点的个数为：(被删节点的右值-被删节点的左值+1)/2，而任何一个节点同时具有唯一的左值和唯一的右值，故删除作废节点后，其他相应节点的左、右值需要调整的幅度应为：减少(被删节点的右值-被删节点的左值+1)。

最后，让我们看看平移节点“电器”，将其和其所有子孙节点移动到节点“食品”之前后，该树会变成什么情况：1商品18+-----------------------------------+14-12电器17-12 2+4食品13+4+----------------------+ 15-12电冰箱16-12 3+4肉类8+4 9+4蔬菜类12+4+-------------------+4+4猪肉5+4 6+4牛肉7+4 10+4白菜11+4大家仔细观察一下交换后同层2个节点和其所有子孙节点左右值的变化，可以发现一个明显的规律，那就是，节点“电器”及其所有子孙节点的左右值均减少12，而节点“食品”及其所有子孙节点的左右值均增加4。

而节点“电器”+其子孙节点的数量为2，节点“食品”+其子孙节点的数量为6，这其中有什么联系吗？还记得我在删除节点的存储过程后面的注释吗？任何一个节点同时具有唯一的左值和唯一的右值。

让我们把节点数量*2，正好和节点左右值需要调整的幅度相等。

由此规律，我们可以编写出类似下面的存储过程来实现节点同层前移的功能：CREATE PROCEDURE[dbo].[MoveNodeUp]@type_id intASdeclare@lft intdeclare@rgt intdeclare@layer intif exists (select1from tree where type_id=@type_id)beginSET XACT_ABORT ONBEGIN TRANSACTIONselect@lft=lft,@rgt=rgt,@layer=layer from TreeView where type_id=@type_idif exists (select*from TreeView where rgt=@lft-1and layer=@layer)begindeclare@brother_lft intdeclare@brother_rgt intselect@brother_lft=lft,@brother_rgt=rgt from TreeView where rgt=@lft-1an d layer=@layerupdate tree set lft=lft-(@brother_rgt-@brother_lft+1) where lft>=@lft and rgt <=@rgtupdate tree set lft=lft+(@rgt-@lft+1) where lft>=@brother_lft and rgt<=@br other_rgtupdate tree set rgt=rgt-(@brother_rgt-@brother_lft+1) where rgt>@brother_ rgt and rgt<=@rgtupdate tree set rgt=rgt+(@rgt-@lft+1) where lft>=@brother_lft+(@rgt-@lft+ 1) and rgt<=@brother_rgtendCOMMIT TRANSACTIONSET XACT_ABORT OFFend注意：节点的同层平移可以采用临时表来做中介，降低代码的复杂度。

不用临时表来处理也行，但是update语句顺序一定要考虑周详。

否则，一旦出现bug，对整个类别表的破坏是惊人的，强烈推荐在做上述工作前对类别表进行完整备份。

同层下移的存储过程和同层上移类似，有兴趣的朋友可以自己动手编写体味一下其中的细节，我就不在这里列出来了。

最后，我对上面这种左右值编码实现无限分级类别树的方案做一个总结：优点：在消除递归的前提下实现了无限分级，而且查询条件是基于整形数字比较的，效率很高。

可以进行先序列表，添加，修改，删除，同层平移等常规操作，基本满足需求。

缺点：由于这种左右值编码的方式和常见的阿拉伯数字直观排序不同，再加上节点在树中的层次，顺序不是直观显示出来，而必须通过简单的公式计算后得到，需要花费一定的时间对其数学模型进行深入理解。

而且，采用该方案编写相关存储过程，新增，删除，同层平移节点需要对整个树进行查询修改，由此导致的代码复杂度，耦合度较高，修改维护的风险较高。

发表于@ 2007年04月26日16:36:00|评论(4 )|编辑新一篇: 自定义MembershipProvider来利用 2.0 Login控件的登陆和修改密码模块 | 旧一篇: 分页存储过程的一点心得老板心里你有多重？确立职场位置，明确自身重要性Leo教你看清前途老板心里你有多重？确立职场位置，明确自身重要性Leo教你看清前途评论#hometohome 发表于2008-05-07 22:17:19 IP: 123.115.4.*请问该如何根据子节点的type_id找出父节点呢？比如：猪肉的父节点是肉类、食品、商品#hometohome 发表于2008-05-07 22:38:54 IP: 123.115.4.*declare @rgt intselect @rgt=rgt from tree where type_id=13select * from tree where rgt>=@rgt and lft<=@rgt order by lft我是楼上的。