介绍了一种使用左右值编码实现无限分级树形结构的数据库设计方法，该方法支持高效的非递归查询，以及节点的添加、删除和平移操作。
之前我介绍过一种按位数编码保存树形结构数据的表设计方法，详情见：
浅谈数据库设计技巧(上)
该设计方案的优点是：只用一条查询语句即可得到某个根节点及其所有子孙节点的先序遍历。由于消除了递归，在数据记录量较大时，可以大大提高列表效率。但是，这种编码方案由于层信息位数的限制，限制了每层能所允许的最大子节点数量及最大层数。同时，在添加新节点的时候必须先计算新节点的位置是否超过最大限制。
上面的设计方案必须预先设定类别树的最大层数以及最大子节点数，不是无限分级，在某些场合并不能采用，那么还有更完美的解决方案吗？通过 google的搜索，我又探索到一种全新的无递归查询，无限分级的编码方案——左右值。原文的程序代码是用php写的，但是通过仔细阅读其数据库表设计说明及相关的sql语句，我彻底弄懂了这种巧妙的设计思路，并在这种设计中新增了删除节点，同层平移的需求（原文只提供了列表及插入子节点的sql语句）。
下面我力图用比较简短的文字，少量图表，及相关核心sql语句来描述这种设计方案：
首先，我们弄一棵树作为例子：
商品
|---食品
| |---肉类
| | |--猪肉
| |---蔬菜类
| |--白菜
|---电器
|--电视机
|--电冰箱
采用左右值编码的保存该树的数据记录如下（设表名为
tree）：
|
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
|
第一次看见上面的数据记录，相信大部分人都不清楚左值（
Lft）和右值（Rgt）是根据什么规则计算出来的，而且，这种表设计似乎没有保存父节点的信息。下面把左右值和树结合起来，请看：
1商品18
+---------------------------------------+
2食品11 12电器17
+-----------------+ +---------------------+
3肉类6 7蔬菜类10 13电视机14 15电冰箱16
4猪肉5 8白菜9
+-----------------+ +---------------------+
3肉类6 7蔬菜类10 13电视机14 15电冰箱16
4猪肉5 8白菜9
请用手指指着上图中的数字，从
1数到18，学习过数据结构的朋友肯定会发现什么吧？对，你手指移动的顺序就是对这棵树的进行先序遍历的顺序。接下来，让我讲述一下如何利用节点的左右值，得到该节点的父节点，子孙节点数量，及自己在树中的层数。
假定我们要对节点“食品”及其子孙节点进行先序遍历的列表，只需使用如下一条
sql语句：
select * from tree where Lft between 2 and 11 order by Lft asc
查询结果如下：
|
Type_id
|
Name
|
Lft
|
Rgt
|
|
2
|
食品
|
2
|
11
|
|
3
|
肉类
|
3
|
6
|
|
4
|
猪肉
|
4
|
5
|
|
5
|
蔬菜类
|
7
|
10
|
|
6
|
白菜
|
8
|
9
|
那么某个节点到底有多少子孙节点呢？很简单，子孙总数 ＝（右值
-左值-1）/2
以节点“食品”举例，其子孙总数＝（
11-2-1）/ 2 ＝ 4
同时，我们在列表显示整个类别树的时候，为了方便用户直观的看到树的层次，一般会根据节点所处的层数来进行相应的缩进，那么，如何计算节点在树中的层数呢？还是只需通过左右值的查询即可，以节点“食品”举例，
sql语句如下：
select count(*) from tree where lft <= 2 and rgt >= 11
为了方便列表，我们可以为
tree表建立一个视图，添加一个层数列，该类别的层数可以写一个自定义函数来计算。该函数如下：
CREATE
FUNCTION
dbo.CountLayer
(
@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
(
@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
然后，我们建立如下视图：
CREATE
VIEW
dbo.TreeView
AS
SELECT
type_id, name, lft, rgt, dbo.CountLayer(type_id)
AS
layer
FROM
dbo.tree
ORDER
BY
lft
GO
给出对于给定某个节点，对该节点及其子孙节点进行先序遍历的存储过程：
CREATE
PROCEDURE
[
dbo
]
.
[
GetTreeListByNode
]
(
@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
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
|
采用左右值编码的设计方案，在进行类别树的遍历时，由于只需进行
2次查询，消除了递归，再加上查询条件都为数字比较，效率极高，类别树的记录条目越多，执行效率越高。看到这里，相信不少人对这种设计方案有所心动了，下面让我们接着看看如何在这种表结构中实现插入、删除、同层平移节点（变更同层节点排序）的功能。
假定我们要在节点“肉类”下添加一个子节点“牛肉”，该树将变成：
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
+-----------------+ +-------------------------+
3肉类6+2 7+2蔬菜类10+2 13+2电视机14+2 15+2电冰箱16+2
+-------------+
4猪肉5 6牛肉7 8+2白菜9+2
看完上图相应节点左右值的变化后，相信大家都知道该如何写相应的
sql脚本吧？下面我给出相对完整的插入子节点的存储过程：
CREATE
PROCEDURE
[
dbo
]
.
[
AddSubNodeByNode
]
(
@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
(
@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
然后，我们删除节点“电视机”，再来看看该树会变成什么情况：
1商品20-2
+-----------------------------------+
2食品13 14电器19-2
+-----------------+
3肉类8 9蔬菜类12 17-2电冰箱18-2
+----------+
+-----------------+
3肉类8 9蔬菜类12 17-2电冰箱18-2
+----------+
4猪肉5 6牛肉7 10白菜11
相应的存储过程如下：
CREATE
PROCEDURE
[
dbo
]
.
[
DelNode
]
@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
<IMG alt="" src="http://images.youkuaiyun.com/syntaxhighlighting/OutliningIndi
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
