关于分级统计的一个问题，请教大家怎么组织SQL语句

Question

结构如下：
       1
     / |     2  3  4
   /    5  6
  /  7  8
有子节点的没有数据，数据来源于没有子节点的点（3，4，6，7，8）
要求统计出所有节点的数据：
ID  sl
1   280
2   150
3   30
4   40
5   150
6   60 
7   70
8   80

Answer 1

--纯属自娱，没什么适用价值，只是试试看1个语句能不能搞出来。
--推荐用存储过程递归做。
--以下语句只支持最多4层分级，还没试过id3位以上的，2位是没问题的。--生成测试数据
declare @t1 table(id int,pid int)
declare @t2 table(id int,s1 int)
insert into @t1
select 1,0
union all select 2,1
union all select 3,1
union all select 4,1
union all select 5,2
union all select 6,2
union all select 7,5
union all select 8,5
union all select 12,5
insert into @t2 
select 3,30
union all select 4,40
union all select 6,60
union all select 7,70
union all select 8,80
union all select 12,20SELECT id = cast(RIGHT(c, charindex('.', reverse(c)) - 1) AS int), s1 =
          (SELECT SUM(s1)
         FROM (SELECT c, s1
                 FROM (SELECT c = isnull
                                   ((SELECT c
                                   FROM (SELECT c = isnull
                                                     ((SELECT c
                                                     FROM (SELECT c = cast(pid AS varchar(2)) 
                                                                   + '.' + cast(id AS varchar(2))
                                                             FROM @t1) t13
                                                     WHERE LEFT(t14.c, charindex('.', t14.c) 
                                                           - 1) = RIGHT(c, charindex('.', 
                                                           reverse(c)) - 1)), 0) + RIGHT(c, len(c) 
                                                 - charindex('.', t14.c) + 1)
                                           FROM (SELECT c = cast(pid AS varchar(2)) 
                                                         + '.' + cast(id AS varchar(2))
                                                   FROM @t1) t14) t11
                                   WHERE LEFT(t7.c, charindex('.', t7.c) - 1) = RIGHT(c, 
                                         charindex('.', reverse(c)) - 1)), 0) + RIGHT(c, len(c) 
                               - charindex('.', t7.c) + 1)
                         FROM (SELECT c = isnull
                                           ((SELECT c
                                           FROM (SELECT c = cast(pid AS varchar(2)) 
                                                         + '.' + cast(id AS varchar(2))
                                                   FROM @t1) t15
                                           WHERE LEFT(t9.c, charindex('.', t9.c) - 1) 
                                                 = RIGHT(c, charindex('.', reverse(c)) - 1)), 0) 
                                       + RIGHT(c, len(c) - charindex('.', t9.c) + 1)
                                 FROM (SELECT c = cast(pid AS varchar(2)) 
                                               + '.' + cast(id AS varchar(2))
                                         FROM @t1) t9) t7) t1 LEFT OUTER JOIN
                       @t2 t2 ON cast(RIGHT(t1.c, charindex('.', reverse(t1.c)) - 1) AS int) 
                       = t2.id) t3
         WHERE t3.c LIKE t4.c + '.%' OR
               t3.c = t4.c)
FROM (SELECT c
        FROM (SELECT c = isnull
                          ((SELECT c
                          FROM (SELECT c = isnull
                                            ((SELECT c
                                            FROM (SELECT c = cast(pid AS varchar(2)) 
                                                          + '.' + cast(id AS varchar(2))
                                                    FROM @t1) t16
                                            WHERE LEFT(t17.c, charindex('.', t17.c) - 1) 
                                                  = RIGHT(c, charindex('.', reverse(c)) - 1)), 0) 
                                        + RIGHT(c, len(c) - charindex('.', t17.c) + 1)
                                  FROM (SELECT c = cast(pid AS varchar(2)) 
                                                + '.' + cast(id AS varchar(2))
                                          FROM @t1) t17) t12
                          WHERE LEFT(t8.c, charindex('.', t8.c) - 1) = RIGHT(c, charindex('.', 
                                reverse(c)) - 1)), 0) + RIGHT(c, len(c) - charindex('.', t8.c) + 1)
                FROM (SELECT c = isnull
                                  ((SELECT c
                                  FROM (SELECT c = cast(pid AS varchar(2)) 
                                                + '.' + cast(id AS varchar(2))
                                          FROM @t1) t18
                                  WHERE LEFT(t10.c, charindex('.', t10.c) - 1) = RIGHT(c, 
                                        charindex('.', reverse(c)) - 1)), 0) + RIGHT(c, len(c) 
                              - charindex('.', t10.c) + 1)
                        FROM (SELECT c = cast(pid AS varchar(2)) 
                                      + '.' + cast(id AS varchar(2))
                                FROM @t1) t10) t8) t5 LEFT OUTER JOIN
              @t2 t6 ON cast(RIGHT(t5.c, charindex('.', reverse(t5.c)) - 1) AS int) = t6.id) t4--返回结果
（所影响的行数为 9 行）
（所影响的行数为 6 行）id          s1          
----------- ----------- 
1           300
2           230
3           30
4           40
5           170
6           60
7           70
8           80
12          20（所影响的行数为 9 行）

Answer 2

--无限制的计算存储过程
create proc p_calc
as
set nocount on
select a.id,a.pid,sl=isnull(b.sl,0),total=-1
into #t 
from ta a left join(
select id,sl=sum(sl) from tb
group by id
)b on a.id=b.id--分级计算
update #t set total=sl
from #t a
where not exists(
select 1 from #t where pid=a.id)
while @@rowcount>0
update #t set total=isnull(a.sl,0)+isnull(b.total,0)
from #t a join(
select pid,total=sum(total) from #t
where total<>0 group by pid
)b on a.id=b.pid
where a.total=-1 and not exists(
select 1 from #t where pid=a.id and total=-1)
select id,sl=total from #t
go--调用存储过程进行计算
exec p_calc

Answer 3

--测试--测试数据
create table ta(ID int,PID int)
insert ta select 1,0
union all select 2,1
union all select 3,1
union all select 4,1
union all select 5,2
union all select 6,2
union all select 7,5
union all select 8,5create table tb(ID int,sl int)
insert tb select 3,30
union all select 4,40
union all select 6,60
union all select 7,70
union all select 8,80
go--计算的存储过程
create proc p_calc
as
set nocount on
select a.id,a.pid,sl=isnull(b.sl,0),total=-1
into #t 
from ta a left join(
select id,sl=sum(sl) from tb
group by id
)b on a.id=b.id--分级计算
update #t set total=sl
from #t a
where not exists(
select 1 from #t where pid=a.id)
while @@rowcount>0
update #t set total=isnull(a.sl,0)+isnull(b.total,0)
from #t a join(
select pid,total=sum(total) from #t
where total<>0 group by pid
)b on a.id=b.pid
where a.total=-1 and not exists(
select 1 from #t where pid=a.id and total=-1)
select id,sl=total from #t
go--调用存储过程进行计算
exec p_calc
go--删除测试
drop table ta,tb
drop proc p_calc/*--测试结果id          sl          
----------- ----------- 
1           280
2           210
3           30
4           40
5           150
6           60
7           70
8           80
--*/

Answer 4

适合无限级别的函数：create table t1(id int,pid int)
create table t2(id int,s1 int)
insert into t1
select 1,0
union all select 2,1
union all select 3,1
union all select 4,1
union all select 5,2
union all select 6,2
union all select 7,5
union all select 8,5
union all select 12,5
insert into t2 
select 3,30
union all select 4,40
union all select 6,60
union all select 7,70
union all select 8,80
union all select 12,20select * from t1
select * from t2alter function fn_test(@a int)
--北风
--2004.7.8
returns int
as
begin
   declare @b int
   declare @t table(a int)
   insert @t select id from t1 where pid=@a
   --if(@@rowcount>0)
   --begin
   while @@rowcount>0
     insert @t 
select id from t1 where pid in(select a from @t) 
and 
id not in(select a from @t)   select @b=sum(s1) from t2 where id in(select a from @t)
   
   --当为NULL时，表示 @a 不在 pid 中
   if(@b is null)
     select @b=sum(s1) from t2 where id=@a
   return @b
end--调用
select id,dbo.fn_test(id) s1 from t1
id          s1          
----------- ----------- 
1           300
2           230
3           30
4           40
5           170
6           60
7           70
8           80
12          20（所影响的行数为 9 行）

Answer 5

alter function fn_test
改为
create function fn_test