How to Store a tree structure in the Database.docx

How to Store a tree structure in the Database.docx

《How to Store a tree structure in the Database.docx》由会员分享，可在线阅读，更多相关《How to Store a tree structure in the Database.docx（14页珍藏版）》请在冰豆网上搜索。

HowtoStoreatreestructureintheDatabase

在关系型数据库中存储和管理树形结构的数据

关键字：

treenode,node_index,rightmostchild_indx

在数据结构中，我们可以通过深度优先来遍历图，树其也是图的一种，我们现在利用深度优先的原理为一颗树的各个节点给一个标识值，这些标识值标记的是遍历过程中，遍历各个节点顺序的序号，假设树的根节点的序号从0开始编号，则以下各课树的节点顺序标识号如下图所示，红色的数字就是对应节点在图中的遍历顺序的序号。

利用深度优先遍历树的过程中为节点编制的节点序号，我们为其取名node_index,节点最右边的子节点的序号我们称之为rightmostchild_indx。

例如，在下图最后一颗包含（A、B、C、D、E、F）节点的树中，C节点的node_index=3,它最右边的子节点是F，所以C的rightmostchild_index=5。

叶子节点的rightmostchild_indx=node_index；如果一个节点只有一个子节点，则该子节点就是它最右边的子节点。

利用在在深度优先遍历方法中生成node_index和rightmostchild_index所蕴含的规律，我们可以在关系型数据库中方便的存储常规的树形结构的数据，在对节点的查询和删除操作方面相对我们大家习惯来操作树型结构所用的递归方法来说，其效率要高的的多，而且在存储空间方面也相对比较合理，对于环，和孤儿树的查询也不会复杂。

图1

新建一个测试库：

TreeStructureDB,新增一张表：

treenode

图2

包含以下字段：

图3

一、插入节点

插入节点一般指插入根节点，或者找到某个节点作为父节点，插入子节点。

按照以下SQL操作进行

----------------------------------------

--**给指定的节点插入子节点

----------------------------------------

declare@parentnode_indexint--存储选中的父节点的node_index

declare@parent_idint--存储选中该的父节点的id

declare@nodenamenvarchar（50）--要插入的子节点的名称

--赋值操作

set@parentnode_index=Value

set@parent_id=（selecttreenode.idfromtreenodewherenode_index=@parentnode_index）

set@nodename=Value

--插入数据的相关操作

UPDATEtreenode

SETtreenode.node_index=treenode.node_index+1

WHEREtreenode.node_index>@parentnode_index;

UPDATEtreenode

SETtreenode.rightmostchild_index=treenode.rightmostchild_index+1

WHEREtreenode.rightmostchild_index>=@parentnode_index;

INSERTINTOtreenode

（node_index,rightmostchild_index,NAME,parent_id）

VALUES（@parentnode_index+1,@parentnode_index+1,@nodename,@parent_id）;

下面我举例说明向表中依次插入A、B、C、D、E、F各条数据。

（1）向表中插入第一条数据，根节点A:

图4

Insertintotreenode

（name,node_index,rightmostchild_index,parent_id）

values（'A',0,0,NULL）

图5

（2）向表中插入第二条数据，根节点A的子节点B:

图6

set@parentnode_index=0

set@parent_id=（selecttreenode.idfromtreenodewherenode_index=@parentnode_index）

set@nodename='C'

图7

（3）向表中插入第三条数据，根节点A的子节点C:

图8

SQL操作类同于步骤二，只需要改变节点名称即可

--赋值操作

set@parentnode_index=0

set@parent_id=（selecttreenode.idfromtreenodewherenode_index=@parentnode_index）

set@nodename='C'

图9

（4）向表中插入第四条数据，根节点A的子节点D:

图9

set@parentnode_index=0

set@parent_id=（selecttreenode.idfromtreenodewherenode_index=@parentnode_index）

set@nodename='D'

图10

（5）向表中插入第五条数据，节点D的子节点E

图11

set@parentnode_index=1

set@parent_id=（selecttreenode.idfromtreenodewherenode_index=@parentnode_index）

set@nodename='E'

图12

（6）向表中插入第六条数据，节点C的子节点F

图13

set@parentnode_index=3

set@parent_id=（selecttreenode.idfromtreenodewherenode_index=@parentnode_index）

set@nodename='F'

图14

（7）向表中插入第六条数据，节点C的子节点G

图15

set@parentnode_index=3

set@parent_id=（selecttreenode.idfromtreenodewherenode_index=@parentnode_index）

set@nodename='G'

图16

2、查询

（1）查询指定节点的所有子节点

图17

--指定的节点的node_index和rightmostchild_index

declare@parentnode_indexint

declare@parentnode_rightchild_indexint

--根据指定的节点的node_index和rightmostchild_index赋值

set@parentnode_index=node_index_value

set@parentnode_rightmostchild_index=rightmostchild_index_value

select*fromtreenodewherenode_index>@parentnode_index

andnode_index<=@parentnode_rightmostchild_index

1、查询A节点下的所有子节点，我们知道预期的结果应该是：

B、C、D、E

A的node_index_value为0，rightmostchild_index_value为6。

set@parentnode_index=0

set@parentnode_rightmostchild_index=6

图18

2、查询C节点下的所有子节点，我们知道预期的结果应该是：

G、F

C的node_index_value为3，rightmostchild_index_value为6。

set@parentnode_index=3

set@parentnode_rightmostchild_index=5

图19

3、查询D节点下的所有子节点，我们知道预期的结果应该是：

E

D的node_index_value为1，rightmostchild_index_value为4。

set@parentnode_index=1

set@parentnode_rightmostchild_index=2

（2）查询指定节点最亲的子节点

方法一：

node_index_value:

指定节点的node_index的值

declare@parentnode_indexint

set@parentnode_index=node_index_value

SELECTt1.*

FROMtreenodet1,treenodet2

WHEREt1.node_index>@parentnode_index

ANDt1.node_index<=t2.rightmostchild_index

ANDt2.node_index=@parentnode_index

AND0=（SELECTCOUNT

（1）

FROMtreenodeq

WHEREnode_index

ANDq.node_index

ANDt1.rightmostchild_index<=q.rightmostchild_index

ANDq.rightmostchild_index<=t2.rightmostchild_index）;

方法二：

上面的方法查询貌似效率有些低了，这个时候parent_id就派上用场了

node_id：

指定节点的ID

declare@parent_idint

set@parent_id==node_id

select*fromdbo.treenodewhereparent_id=@parent_id

（3）查询从根节点到指定节点的路径经过的节点

declare@node_indexint

set@node_index=node_index_value

SELECTt1.*

FROMtreenodet1,treenodet2

WHEREt1.node_index<=node_index_value

ANDt1.rightmostchild_index>=t2.rightmostchild_index

ANDt2.rightmostchild_index=node_index_value

3、删除节点

删除指定的节点，删除节点的同时需要更新相关节点的信息，下面的sql中需要传入指定节点的node_index的值node_index_value:

declare@node_index_valueint

declare@node_parent_idint

declare@node_idint

set@node_index_value=node_index_value

set@node_parent_id=（selectparent_idfromtreenodewherenode_index=@node_index_value）

set@node_id=（selectidfromtreenodewherenode_index=@node_index_value）

DELETEtreenode

WHEREtreenode.node_index=@node_index_value;

UPDATEtreenode

SETtreenode.node_index=treenode.node_index-1

WHEREtreenode.node_index>@node_index_value;

UPDATEtreenode

SETtreenode.rightmostchild_index=treenode.rightmostchild_index-1

WHEREtreenode.rightmostchild_index>@node_index_value;

UPDATEtreenode

SETtreenode.parent_id=@node_parent_id

WHEREtreenode.parent_id=@node_id;

Demo:

删除节点C，节点C的node_index_value=3，则修改上面的SQL：

set@node_index_value=3

图20

执行后的到的结果：

4、错误数据检查，环，孤儿树

检查孤儿树：

如果一个节点是孤儿，则我们查询从根节点到指定节点的路径经过的节点中必定不会包含根节点（参考查询章节）。

检查环：

正常数据，一个节点一般有且仅有一个最右子节点，如果存在环，必定存在一个节点的最右子节点不止一个,则下面的查询能够得到相应的记录：

selectnode_indexfrom

（selectCOUNT（rightmostchild_index）

asrightmostchild_count,node_indexfromdbo.treenode

groupbynode_index

）t

wheret.rightmostchild_count>1

参考：