添加到二叉树的问题，请高手赐招，急

[wgh968]

CREATE PROCEDURE dbo.addtree
@id int,@who varchar(16)
as

二叉树的添加，要把who添加到子树id下面的最近的空位上，怎样的算法效率最高，请高手帮我写个存储过程吧，急啊，谢过了

[山林73]

在数据量较大时, 可以用以下方法减少
@t1, @t2 之间倒数据:



declare @t1 table ( id int PRIMARY KEY )
declare @t2 table ( id int PRIMARY KEY )

declare @id0 as int, @id2 as int, @id_x as int , @maxlevel int, @ts int

set @maxlevel= 10
set @ts = 1
set @id0 =1 -- 起始的目标节点

insert into @t1 ( id ) values ( @id0 )

while @maxlevel >0
begin
-- if @ts =1
-- begin
-- 返回下一层的子节点
delete from @t2
insert into @t2 (id)
select aaa.id from aaa inner join @t1 b on aaa.pid =b.id
-- 无子节点返回当前层下的一个节点
if (select count(*) from @t2 ) <1
begin
SELECT top 1 @id_x = id FROM @t1
break
end
-- 分支数(子节点)小于2的节点，即最近的空位
SELECT top 1 @id_x = id FROM @t2 b
WHERE ((SELECT COUNT(*) FROM aaa WHERE aaa.pid = b.id) < 2)

if @id_x is not null break

-- set @ts = 2
-- end
-- else
-- begin
delete from @t1
insert into @t1 (id)
select aaa.id from aaa inner join @t2 b on aaa.pid =b.id

if (select count(*) from @t1 ) <1
begin
SELECT top 1 @id_x = id FROM @t2
break
end

SELECT top 1 @id_x = id FROM @t1 b
WHERE ((SELECT COUNT(*) FROM aaa WHERE aaa.pid = b.id) < 2)

if @id_x is not null break

-- set @ts = 1
-- end

set @maxlevel = @maxlevel -1
end

select @id_x

[山林73]

@maxlevel 是用于控制异常情况下可能引起的死循环，可以设置大些

[山林73]

declare @id0 as int, @id2 as int, @id_x as int , @maxlevel int, @ts int

set @id0 =1 -- 起始的目标节点

if (SELECT COUNT(*) FROM aaa WHERE aaa.pid = @id0) <2
begin
set @id_x = @id0
end
else
begin

declare @t1 table ( id int PRIMARY KEY )
declare @t2 table ( id int PRIMARY KEY )
set @maxlevel= 10
set @ts = 1

insert into @t1 ( id ) values ( @id0 )

while @maxlevel >0
begin
-- if @ts =1
-- begin
-- 返回下一层的子节点
delete from @t2
insert into @t2 (id)
select aaa.id from aaa inner join @t1 b on aaa.pid =b.id

-- 分支数(子节点)小于2的节点，即最近的空位
SELECT top 1 @id_x = id FROM @t2 b
WHERE ((SELECT COUNT(*) FROM aaa WHERE aaa.pid = b.id) < 2)

if @id_x is not null break

-- set @ts = 2
-- end
-- else
-- begin
delete from @t1
insert into @t1 (id)
select aaa.id from aaa inner join @t2 b on aaa.pid =b.id

SELECT top 1 @id_x = id FROM @t1 b
WHERE ((SELECT COUNT(*) FROM aaa WHERE aaa.pid = b.id) < 2)

if @id_x is not null break

-- set @ts = 1
-- end

set @maxlevel = @maxlevel -1
end
end
select @id_x

[wgh968]

我最后加了一个字段，表示该子节电是否满，这样判断的时候简单些，谢谢楼上各位的热心帮助。

[ghostzxp]

关注.

[wgh968]

补充，表字段是 id pid who 特别邀请斑竹大哥帮忙

[wgh968]

翻遍了好像没有类似问题提出

[山林73]

declare @t1 table (
id int PRIMARY KEY ,
pid int ,
who varchar (50)
)

declare @t2 table (
id int PRIMARY KEY ,
pid int ,
who varchar (50)
)

declare @id0 as int, @id2 as int, @id_x as int , @maxlevel int

set @maxlevel= 10
set @id0 =1



insert into @t1 ( id ) values ( @id0 )

while @maxlevel >0
begin
insert into @t2 (id)
select aaa.id from aaa inner join @t1 b on aaa.pid =b.id

if (select count(*) from @t2 ) <1
begin
SELECT top 1 @id_x = id FROM @t1
break
end

SELECT top 1 @id_x = id FROM @t2 b
WHERE ((SELECT COUNT(*) FROM aaa WHERE aaa.pid = b.id) < 2)

if @id_x is not null break

delete from @t1

insert into @t1 (id)
select id from @t2

set @maxlevel = @maxlevel -1
end

select @id_x

/*

1 ss
2 1 adf
3 1
4 2
5 3
6 2
7 3
8 4
9 4
10 5
11 5
>>>>>>>>>
6

*/

[lovcal]

你说得不太明白啊，你的表是用来描述二叉树的？还是……
把你想干什么描述清楚一点

[山林73]

declare @t1 table (
id int PRIMARY KEY )

declare @t2 table (
id int PRIMARY KEY )

[山林73]

CREATE TABLE [dbo].[aaa] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[pid] [int] NULL ,
[who] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[aaa] WITH NOCHECK ADD
CONSTRAINT [PK_aaa] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[aaa] ADD
CONSTRAINT [FK_aaa_aaa] FOREIGN KEY
(
[pid]
) REFERENCES [dbo].[aaa] (
[id]
)
GO

[山林73]

delete from @t1

insert into @t1 (id)
select id from @t2
--!!!!
delete from @t2

[wgh968]

表就是一个二叉树，现在要添加一个，得找到一个添加的节点啊，这个节点必须是最近的节点，也就是说尽量构成满二叉树

[wgh968]

yaozw_mountain(山林）的我再看看，还没读懂