以下面一个实际的计算机目录结构为例,研究左右值编码存储树形结构的算法
计算机
|----c:
| |----program
| |----users
| | |----wanghua
| | |----zixin
| |----windows
| |----system
| |----system32
|----d:
| |----download
| |----software
|----e:
对该结构进行左右值编码:
得到如下的数据表(treetable)
id name left right
1 计算机 1 26
2 c: 2 17
3 d: 18 23
4 e: 24 25
5 program 3 4
6 users 5 10
7 windows 11 16
8 download 19 22
9 wanghua 6 7
10 zixin 8 9
11 system 12 13
12 system32 14 15
13 software 20 21
根据该表,生成一个包含层数的视图(treetableview):
id name left right layer parent
1 计算机 1 26 1 0
2 c: 2 17 2 1
3 d: 18 23 2 1
4 e: 24 25 2 1
5 program 3 4 3 2
6 users 5 10 3 2
7 windows 11 16 3 2
8 download 19 22 3 3
9 wanghua 6 7 4 6
10 zixin 8 9 4 6
11 system 12 13 4 7
12 system32 14 15 4 7
13 software 20 21 4 8
视图算法:
create view dbo.treetableview
as
selelct id, name, [left], [right],treelayer(id) as layer , parentnode(id) as parent from treetable
go
计算层数的函数
sql server
create function treelayer( @id int )
returns int
as
begin
declare @result int
set @result=0
declare @left int
declare @right int
if exists (select 1 from treetable where id=@id)
begin
select @left=[left],@right=[right] from treetable where id=@id
select @result = count(*) from treetable where [left]<= @left and [right]>= @right
end
return @result
end
oracle
CREATE OR REPLACE FUNCTION "TREELAYER"
(
fid number
)
return number
as
rid number;fleft number;fright number;fcount number;
begin
rid:=0;
select count(*) into fcount from treetable where id=fid;
if fcount > 0 then
select left,right into fleft,fright from treetable where id=fid;
select count(*) into rid from treetable where left<=fleft and right>=fright;
end if;
return rid;
end;
计算父节点的函数
sql server
create function parentnode(@id int)
returns int
as
begin
declare @result int
declare @left int
declare @right int
set @result=-1
if exists(select 1 from treetable where id=@id)
begin
select @left=left,@right=right from treetable where id=@id
if(@left = 1)
set @result = 0
else
select top (1) @result=id from treetable where [left]<@left and [right]>@right order by left desc
end
end
go
oracle
CREATE OR REPLACE FUNCTION "PARENTNODE"
(
fid number
)
return number
as
rid number;fleft number;fright number;fcount number;
begin
rid:=-1;
select count(*) into fcount from treetable where id=fid;
if fcount > 0 then
select left,right into fleft,fright from treetable where id=fid;
if fleft = 1 then
rid:= 0;
else
select id into rid from(select tb.*,rownum from treetable tb where lfet<fleft and right>fright order by left desc) where rownum=1;
end if;
end if;
return rid;
end;
子孙节点
select treetableview.*
from treetableview,treetable
where treetable.id=@id
and treetableview.[left] between treetable.[left] and treetable.[right]
子节点
select treetableview.*
from treetableview,treetable
where treetable.id=@id and treetableview.[left] between treetable.[left] and treetable.[right]
and treetableview.layer = treetable.layer + 1
叶子节点
select * from treetableview where [left] = [right] - 1
祖先节点
select treetableview.*
from treetableview,treetable
where treetable.id=@id and treetableview.[left]<treetable.[left] and treetableview.[right] > treetable.[right]
order by [left] desc
父节点
兄弟节点
select treetableview.*
from treetableview,treetableview tv
where tv.id=@id and treetableview.id!=@id and treetableview.parent=tv.parent
插入根节点
CREATE PROCEDURE InsertRootNode
@name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
declare @left int
declare @right int
if exists(select 1 from treetable where [left]=1)
begin
select @left=[left],@right=[right]
from treetable
where [left]=1
update treetable
set [left]=[left]+1,[right]=[right]+1
where [left]>=@left and [right]<=@right
insert into treetable([name],[left],[right])
values (@name,@left,@right+1)
end
else
insert into treetable([name],[left],[right])
values(@name,1,2)
END
GO
插入兄弟节点
左插
CREATE PROCEDURE InsertLeftNode
@id int,
@name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
declare @left int
if exists(select 1 from treetable where id=@id and [left]!=1)
begin
select @left=[left] from treetable where id=@id
update treetable
set [left]=[left]+2
where [left]>=@left
update treetable
set [right]=[right]+2
where [right]>@left
insert into treetable(name,[left],[right])
values(@name,@left,@left+1)
end
END
GO
右插
CREATE PROCEDURE InsertRightNode
@id int,
@name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
declare @right int
if exists(select 1 from treetable where id=@id and [left]!=1)
begin
select @right=[right] from treetable where id=@id
update treetable
set [left]=[left]+2
where [left]>@right
update treetable
set [right]=[right]+2
where [right]>@right
insert into treetable(name,[left],[right])
values(@name,@right+1,@right+2)
end
END
GO
插入子节点
插入第一个子节点CREATE PROCEDURE InsertLastSubnode
@id int,
@name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
declare @left int
if exists(select 1 from treetable where id=@id)
begin
select @left=[left] from treetable where id=@id
update treetable
set [left]=[left]+2
where [left]>@left
update treetable
set [right]=[right]+2
where [right]>@left
insert into treetable(name,[left],[right])
values(@name,@left+1,@left+2)
end
END
GO
插入最后一个子节点
CREATE PROCEDURE InsertLastSubnode
@id int,
@name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
declare @right int
if exists(select 1 from treetable where id=@id)
begin
select @right=[right] from treetable where id=@id
update treetable
set [left]=[left]+2
where [left]>@right
update treetable
set [right]=[right]+2
where [right]>=@right
insert into treetable(name,[left],[right])
values(@name,@right-1,@right)
end
END
GO
删除节点
CREATE PROCEDURE DeleteNode
@id int
AS
BEGIN
SET NOCOUNT ON;
declare @left int
declare @right int
if exists(select 1 from treetable where id=@id)
begin
select @left=[left],@right=[right] from treetable where id=@id
delete from treetable
where [left]>=@left and [right]<=@right
update treetable
set [left]=[left]- ((@right+1)-@left)
where [left]>@left
update treetable
set [right]=[right]-((@right+1)-@left)
end
END
GO