左右编号树型结构

最新推荐文章于 2022-11-29 15:58:19 发布

小东1989

最新推荐文章于 2022-11-29 15:58:19 发布

阅读量673

点赞数

分类专栏：数据结构文章标签：数据结构算法

本文链接：https://blog.csdn.net/yangxiaodong1989/article/details/8881249

版权

数据结构专栏收录该内容

1 篇文章 0 订阅

订阅专栏

以下面一个实际的计算机目录结构为例，研究左右值编码存储树形结构的算法

计算机

|----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