关闭

求二叉树的算法

358人阅读 评论(0) 收藏 举报
 

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1915次
    • 积分:49
    • 等级:
    • 排名:千里之外
    • 原创:3篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章存档