求二叉树的算法

 

1、题：

有如下表，记录了分左右区的2叉树记录。其中：LorR字段，-1 表示顶级，1 表示左边，2表示右边；Parent_id子段，-1 表示顶级，其它表示上级父ID;User_id字段值是唯一的。

User_idParent_idLorR

1-1-1

211

312

421

522

631

......

请书写一个存储过程，返回给定User_id 的左、右区中小区(网络点数少一点的区，一样多则左边优先)的最下一个新节点（如有左右2个空节点，左边优先）。

 

格式：

P_GetNewNode (@User_id int, @Node_id int output, @LorR int output)

AS

......

set @Node_id = ...

set @LorR = ...

GO

 

 

create table BTree(UserID int, ParentID int, LorR int)

go—为了便于测试，我多写了几行

insert BTree select 1, -1, -1

union all select 2, 1, 1

union all select 3, 1, 2

union all select 4, 2, 1

union all select 5, 2, 2

union all select 6, 3, 1

union all select 7, 4, 1

union all select 8, 6, 1

union all select 9, 6, 2

union all select 10, 7, 1

union all select 11, 8, 1

union all select 12, 8, 2

union all select 13, 9, 2

union all select 14, 10, 2

Go

 

create function fnGetSubNodes(@UserID int)—得到子节点个数

returns int as

begin

declare @Total int, @SubNodeL int, @SubNodeR int

set @Total=0

select @SubNodeL=UserID from BTree where ParentID=@UserID and LorR=1

select @SubNodeR=UserID from BTree where ParentID=@UserID and LorR=2

if @SubNodeL is not null

set @Total=1+dbo.fnGetSubNodes(@SubNodeL)

if @SubNodeR is not null

set @Total=@Total+1+dbo.fnGetSubNodes(@SubNodeR)

return @Total

end

go

 

 

--得到最深的节点层数，通过递归去找最深的值，如果左右都有子节点，再判断谁的子节点层次深，总之每次在递归返回都是最深的节点值（写这段我也差点被搅晕了，后来才在每上IF上加了注释，为了便于以后阅读，又加了些）

create function fnGetSubLayers(@UserID int, @Layer int)

returns varchar(100) as

begin

declare @result varchar(100)

set @result=cast(@Layer as varchar)+'|'+cast(@UserID as varchar)

declare @NID int, @NID2 int, @LL varchar(10), @LR varchar(10)

select @NID=UserID from BTree where ParentID=@UserID and LorR=1

select @NID2=UserID from BTree where ParentID=@UserID and LorR=2

if @NID is not null

begin

set @LL=dbo.fnGetSubLayers(@NID,@Layer+1)

if @NID2 is not null

begin

set @LR=dbo.fnGetSubLayers(@NID2,@Layer+1)

if(convert(int,left(@LL,charindex('|',@LL)-1))>=convert(int,left(@LR,charindex('|',@LR)-1)))—在两个都不为空的情况下再判断哪个层深@result就等于哪个返回值

set @result=@LL

else

set @result=@LR

end

else—当右边为空时，@result等于左边返回值

set @result=@LL

end

else if@NID2 is not null—如果左为空，而右不为空，调用并返回右边值

begin

set @LR=dbo.fnGetSubLayers(@NID2,@Layer+1)

set @result=@LR

end

return @result

end

go

 

create procedure spGetNewNode(@UserID int, @NodeID int output, @LorR int output)

AS

BEGIN

declare @SubNodeL int, @SubNodeR int, @Layer varchar(100)

select @Layer='0|'+cast(@UserID as varchar)

select @SubNodeL=UserID from BTree where ParentID=@UserID and LorR=1

select @SubNodeR=UserID from BTree where ParentID=@UserID and LorR=2

if @SubNodeL is not null

if @SubNodeR is not null

if(dbo.fnGetSubNodes(@SubNodeL)<=dbo.fnGetSubNodes(@SubNodeR))

set @Layer=dbo.fnGetSubLayers(@SubNodeL,0)

else

set @Layer=dbo.fnGetSubLayers(@SubNodeR,0)

else—如果右边为空，调用左边和上面的类似

set @Layer=dbo.fnGetSubLayers(@SubNodeL,0)

else if @SubNodeR is not null

set @Layer=dbo.fnGetSubLayers(@SubNodeR,0)

select @NodeID=convert(int,right(@Layer,len(@Layer)-charindex('|',@Layer)))

select @LorR=LorR from BTree where UserID=@NodeID

END

GO

 

--可以显示一下

declare @UserID int, @NodeID int, @LorR int

set @UserID=1

print('UserIDNodeIDLorR')

while @UserID<=14

begin

exec dbo.spGetNewNode @UserID, @NodeID output, @LorR output

print(cast(@UserID as varchar)+''+cast(@NodeID as varchar)+''+cast(@LorR as varchar))

set @UserID=@UserID+1

end

 

go

 

--到最后你也可以删掉所用的东东

drop function dbo.fnGetSubLayers

drop function dbo.fnGetSubNodes

drop procedure dbo.spGetNewNode

drop table BTree

作者简介：

陈茂华，男，四川达州人，主要研究方向：办公自动化信息系统，GIS系统

Email:cmh-163@163.com