以前写的一段SQL代码，处理Btree的

/*
2 SQLServer与算法：

有如下表，记录了分左右区的2叉树记录。其中：LorR字段，-1 表示顶级，1 表示左边，2表示右边；Parent_id子段，-1 表示顶级，其它表示上级父ID;User_id字段值是唯一的。
User_id Parent_id LorR
1 -1 -1
2 1 1
3 1 2
4 2 1
5 2 2
6 3 1
......
请书写一个存储过程，返回给定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

--select * from BTree
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
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)))
    set @result=@LL
   else
    set @result=@LR
  end
  else
   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( 'UserID NodeID LorR ')
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

/*
UserID NodeID LorR
1  14  2
2  5  2
3  11  1
4  14  2
5  5  2
6  13  2
7  14  2
8  11  1
9  13  2
10  14  2
11  11  1
12  12  2
13  13  2
14  14  2
*/