有关树(家族)的问题

查找所有子节点的问题

create table a( UserID int,     UserName  nvarchar(10),    Ratio int,     Amount int,    FatherID int)
insert into a
select    1,    N'田方',     12,       200,        0 union all
select    2,    N'张三',      8,       100,        1 union all
select    3,    N'李四',     12,       200,        1 union all
select    4,    N'王五',      6,       130,        2 union all
select    5,    N'杨六',      9,       200,        3 union all
select    6,    N'陈七',      4,       190,        2
go

create function f_cid(
@id int
)returns @re table(userid int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.userid,@l
from a,@re b
where a.fatherid=b.userid and b.[level]=@l-1
end
return
end
go

select a.*,层次=b.[level],amount*ratio/100 as fee  from a,f_cid(2)b
where a.userid=b.userid
-----------------
select sum(fee)
from(
select a.*,层次=b.[level],amount*ratio/100 as fee  from a,f_cid(2)b
where a.userid=b.userid
)t
go

drop function f_cid
drop table a