树层的汇总统计

Question

create   table   a(id int ,PId int,  Name   varchar(10))      insert   a   select   1,0,'电脑' -- 1
   union   all  select   5,1,'显示器'---2
   union   all  select   8,5,'显像管'----3
   union   all  select   9,5,'扇热片'----3
   union   all  select   18,1,'键盘'---2
   union   all  select   19,18,'螺丝'----3
   union   all  select   20,18,'键扭'----3   union   all  select   2,0,'房子' ---1
   union   all  select   11,2,'电视'---2
   union   all  select   13,11,'机顶合'----3
   union   all  select   14,11,'遥控器'----3
   union   all  select   21,2,'沙发'---2
   union   all  select   22,21,'沙发套'----3
   union   all  select   23,21,'靠枕'----3   union   all  select   4,0,'日用品'---1
   union   all  select   12,4,'洗涤用品'---2
   union   all  select   15,12,'牙膏'----3
   union   all  select   16,12,'毛巾'----3
   union   all  select   17,12,'衬衣'----3
   union   all  select   24,4,'床上用品'---2
   union   all  select   25,24,'床套'----3
   union   all  select   26,24,'被单'----3
   union   all  select   27,24,'枕套'----3--每一分支树得层数相同，数据保存仅保存最后一层数据 create   table   b(bid int ,Id int,  [mMoney]   int)   
    insert   b   select   1,8,10
     union   all  select   2,9,30  
      union   all  select   14,19,21
     union   all  select   15,20,10
     union   all  select   3,13,2
     union   all  select   4,14,18
     union   all  select   12,22,21
     union   all  select   13,23,21    union   all  select   5,15,2
    union   all  select   6,16,21
    union   all  select   7,17,8  
     union   all  select   9,25,21
     union   all  select   10,26,8 
     union   all  select   11,27,7  关联统计想得到这样结果 分层统计。即将该层下的所有的数据汇总到当前层
可以的话 用存储过程 只需要传入 统计的层 等其它条件  
如果统计第一层 那么得到的结果类型            合计
------------- -----------
 电脑           71
 房子           62  
 日用品         67如果统计第二层 那么得到的结果类型            合计
------------- -----------
 显示器           40
 键盘             31
 电视             20
 沙发             42
 洗涤用品         31
 床上用品         36

Answer 1

DECLARE @a TABLE(id int ,PId int,  Name   nvarchar(10))   
insert   @a   select   1,0,N'电脑' -- 1
   union   all  select   5,1,N'显示器'---2
   union   all  select   8,5,N'显像管'----3
   union   all  select   9,5,N'扇热片'----3
   union   all  select   18,1,N'键盘'---2
   union   all  select   19,18,N'螺丝'----3
   union   all  select   20,18,N'键扭'----3
   union   all  select   2,0,N'房子' ---1
   union   all  select   11,2,N'电视'---2
   union   all  select   13,11,N'机顶合'----3
   union   all  select   14,11,N'遥控器'----3
   union   all  select   21,2,N'沙发'---2
   union   all  select   22,21,N'沙发套'----3
   union   all  select   23,21,N'靠枕'----3
   union   all  select   4,0,N'日用品'---1
   union   all  select   12,4,N'洗涤用品'---2
   union   all  select   15,12,N'牙膏'----3
   union   all  select   16,12,N'毛巾'----3
   union   all  select   17,12,N'衬衣'----3
   union   all  select   24,4,N'床上用品'---2
   union   all  select   25,24,N'床套'----3
   union   all  select   26,24,N'被单'----3
   union   all  select   27,24,N'枕套'----3
DECLARE @b TABLE(bid int ,Id int,  [mMoney]   int)   
    insert   @b   select   1,8,10
     union   all  select   2,9,30  
      union   all  select   14,19,21
     union   all  select   15,20,10
     union   all  select   3,13,2
     union   all  select   4,14,18
     union   all  select   12,22,21
     union   all  select   13,23,21    union   all  select   5,15,2
    union   all  select   6,16,21
    union   all  select   7,17,8  
     union   all  select   9,25,21
     union   all  select   10,26,8 
     union   all  select   11,27,7 DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @a
WHERE PID=0
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT cast(a.ID as varchar(10)),@Level,b.Sort+','+cast(a.ID as varchar(10))
FROM @a a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
ENDdeclare @lsb table(sort varchar(30),mMoney money)
insert @lsb
select t.sort,b.mMoney 
from @t_level t LEFT JOIN @b b on ','+t.sort+',' like '%,'+cast(b.id as varchar(10))+',%' 
where t.level=2 
select  f.id,f.name,sum(e.mMoney)
from  @t_level d left join 
  @lsb e on charindex(d.sort,e.sort)>0
inner join @a f on d.id=f.id 
where level=1
GROUP BY f.id,f.name
order by f.id
id          name                             
----------- ---------- --------------------- 
5           显示器        40.0000
11          电视         20.0000
12          洗涤用品       31.0000
18          键盘         31.0000
21          沙发         42.0000
24          床上用品       36.0000(6 row(s) affected)

Answer 2

DECLARE @a TABLE(id int ,PId int,  Name   nvarchar(10))   
insert   @a   select   1,0,N'电脑' -- 1
   union   all  select   5,1,N'显示器'---2
   union   all  select   8,5,N'显像管'----3
   union   all  select   9,5,N'扇热片'----3
   union   all  select   18,1,N'键盘'---2
   union   all  select   19,18,N'螺丝'----3
   union   all  select   20,18,N'键扭'----3
   union   all  select   2,0,N'房子' ---1
   union   all  select   11,2,N'电视'---2
   union   all  select   13,11,N'机顶合'----3
   union   all  select   14,11,N'遥控器'----3
   union   all  select   21,2,N'沙发'---2
   union   all  select   22,21,N'沙发套'----3
   union   all  select   23,21,N'靠枕'----3
   union   all  select   4,0,N'日用品'---1
   union   all  select   12,4,N'洗涤用品'---2
   union   all  select   15,12,N'牙膏'----3
   union   all  select   16,12,N'毛巾'----3
   union   all  select   17,12,N'衬衣'----3
   union   all  select   24,4,N'床上用品'---2
   union   all  select   25,24,N'床套'----3
   union   all  select   26,24,N'被单'----3
   union   all  select   27,24,N'枕套'----3
DECLARE @b TABLE(bid int ,Id int,  [mMoney]   int)   
    insert   @b   select   1,8,10
     union   all  select   2,9,30  
      union   all  select   14,19,21
     union   all  select   15,20,10
     union   all  select   3,13,2
     union   all  select   4,14,18
     union   all  select   12,22,21
     union   all  select   13,23,21    union   all  select   5,15,2
    union   all  select   6,16,21
    union   all  select   7,17,8  
     union   all  select   9,25,21
     union   all  select   10,26,8 
     union   all  select   11,27,7 DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @a
WHERE PID=0
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT cast(a.ID as varchar(10)),@Level,b.Sort+','+cast(a.ID as varchar(10))
FROM @a a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
ENDdeclare @lsb table(sort varchar(30),mMoney money)
insert @lsb
select t.sort,b.mMoney 
from @t_level t LEFT JOIN @b b on ','+t.sort+',' like '%,'+cast(b.id as varchar(10))+',%' 
where t.level>1    ----- 如果子节点在两层以上，把条件变成t.level>1
select  f.id,类型=f.name,合计=sum(e.mMoney)
from  @t_level d left join 
  @lsb e on charindex(d.sort,e.sort)>0
inner join @a f on d.id=f.id 
where level=1     ----- 想查询哪一层的汇总就变成相应的数值，根节点是0
GROUP BY f.id,f.name
order by f.id

Answer 3

--test data 
create   table   a(id int ,PId int,  Name   varchar(10))      insert   a   select   1,0,'电脑' -- 1
   union   all  select   5,1,'显示器'---2
   union   all  select   8,5,'显像管'----3
   union   all  select   9,5,'扇热片'----3
   union   all  select   18,1,'键盘'---2
   union   all  select   19,18,'螺丝'----3
   union   all  select   20,18,'键扭'----3   union   all  select   2,0,'房子' ---1
   union   all  select   11,2,'电视'---2
   union   all  select   13,11,'机顶合'----3
   union   all  select   14,11,'遥控器'----3
   union   all  select   21,2,'沙发'---2
   union   all  select   22,21,'沙发套'----3
   union   all  select   23,21,'靠枕'----3   union   all  select   4,0,'日用品'---1
   union   all  select   12,4,'洗涤用品'---2
   union   all  select   15,12,'牙膏'----3
   union   all  select   16,12,'毛巾'----3
   union   all  select   17,12,'衬衣'----3
   union   all  select   24,4,'床上用品'---2
   union   all  select   25,24,'床套'----3
   union   all  select   26,24,'被单'----3
   union   all  select   27,24,'枕套'----3go create   table   b(bid int ,Id int,  [mMoney]   int)   
    insert   b   select   1,8,10
     union   all  select   2,9,30  
      union   all  select   14,19,21
     union   all  select   15,20,10
     union   all  select   3,13,2
     union   all  select   4,14,18
     union   all  select   12,22,21
     union   all  select   13,23,21    union   all  select   5,15,2
    union   all  select   6,16,21
    union   all  select   7,17,8  
     union   all  select   9,25,21
     union   all  select   10,26,8 
     union   all  select   11,27,7 
go
-----------------------------------------create function getSumMoney(
@id int
)returns int
as 
begin
declare @s int
select @s=isnull(sum(mMoney),0) from b where bid=@id
if exists(select * from a where pid=@id)
select @s=@s+isnull(sum(dbo.getSumMoney(id)),0) from a where pid=@id
return @s
end
go
create procedure stat
@n int --层数 n>0
as
begin
declare @str varchar(8000)
set @str='select id from a where pid=0'
set @n=@n-1
while @n>0
begin
set @str='select id from a where pid in ('+@str+')'
set @n=@n-1
end
set @str='select name,dbo.getsummoney(id) as totalmoney from a where id in(' +@str+')'
exec(@str)
end
go
--test
exec stat 1
exec stat 2------------
drop table a
drop table b
drop function dbo.getsummoney
drop proc stat

Answer 4

leo_lesley(leo)  加个条件 可以统计 某节点 例：只显示 电脑或者房子等。ID进行传入 1，2就统计该两类

Answer 5

对了 leo_lesley(leo) 第一层
统计数据有点出入
id          类型         合计                    
----------- ---------- --------------------- 
1           电脑         164.0000
2           房子         139.0000
4           日用品        85.0000

Answer 6

--完整 实际结果 
DECLARE @a TABLE(id int ,PId int,  Name   nvarchar(10))   
insert   @a   select   1,0,N'电脑' -- 1
   union   all  select   5,1,N'显示器'---2
   union   all  select   8,5,N'显像管'----3
   union   all  select   9,5,N'扇热片'----3
   union   all  select   18,1,N'键盘'---2
   union   all  select   19,18,N'螺丝'----3
   union   all  select   20,18,N'键扭'----3
   union   all  select   2,0,N'房子' ---1
   union   all  select   11,2,N'电视'---2
   union   all  select   13,11,N'机顶合'----3
   union   all  select   14,11,N'遥控器'----3
   union   all  select   21,2,N'沙发'---2
   union   all  select   22,21,N'沙发套'----3
   union   all  select   23,21,N'靠枕'----3
   union   all  select   4,0,N'日用品'---1
   union   all  select   12,4,N'洗涤用品'---2
   union   all  select   15,12,N'牙膏'----3
   union   all  select   16,12,N'毛巾'----3
   union   all  select   17,12,N'衬衣'----3
   union   all  select   24,4,N'床上用品'---2
   union   all  select   25,24,N'床套'----3
   union   all  select   26,24,N'被单'----3
   union   all  select   27,24,N'枕套'----3
DECLARE @b TABLE(bid int ,Id int,  [mMoney]   int,UnitId)   
    insert   @b   select   1,8,10,1
     union   all  select   2,9,30  ,1
      union   all  select   14,19,21,1
     union   all  select   15,20,10,1
     union   all  select   3,13,2,1
     union   all  select   4,14,18,1
     union   all  select   12,22,21,1
     union   all  select   13,23,21,1    union   all  select   5,15,2,1
    union   all  select   6,16,21,1
    union   all  select   7,17,8 ,1 
     union   all  select   9,25,21,1
     union   all  select   10,26,8 ,1
     union   all  select   11,27,7,1 
--
    insert   @b   select   16,8,10,2
     union   all  select   17,9,30  ,2
      union   all  select   18,19,21,2
     union   all  select   18,20,10,2
     union   all  select   20,13,2,2
     union   all  select   21,14,18,2
     union   all  select   22,22,21,2
     union   all  select   23,23,21,2    union   all  select   24,15,2,2
    union   all  select   25,16,21,2
    union   all  select   26,17,8 ,2 
     union   all  select   27,25,21,2
     union   all  select   28,26,8 ,2
     union   all  select   29,27,7,2 DECLARE @c TABLE(UnitId int ,Name)  
          insert   @c   select   1,'单位1'
      union   all  select   2,'单位2'
        --.....
统计结果格式id          类型          单位1      单位2      --.....              
----------- ---------- -----------   ---------- 
5           显示器           
11          电视          
12          洗涤用品        
18          键盘          
21          沙发          
24          床上用品        

Answer 7

DECLARE @a TABLE(id int ,PId int,  Name   nvarchar(10))   
insert   @a   select   1,0,N'电脑' -- 1
   union   all  select   5,1,N'显示器'---2
   union   all  select   8,5,N'显像管'----3
   union   all  select   9,5,N'扇热片'----3
   union   all  select   18,1,N'键盘'---2
   union   all  select   19,18,N'螺丝'----3
   union   all  select   20,18,N'键扭'----3
   union   all  select   2,0,N'房子' ---1
   union   all  select   11,2,N'电视'---2
   union   all  select   13,11,N'机顶合'----3
   union   all  select   14,11,N'遥控器'----3
   union   all  select   21,2,N'沙发'---2
   union   all  select   22,21,N'沙发套'----3
   union   all  select   23,21,N'靠枕'----3
   union   all  select   4,0,N'日用品'---1
   union   all  select   12,4,N'洗涤用品'---2
   union   all  select   15,12,N'牙膏'----3
   union   all  select   16,12,N'毛巾'----3
   union   all  select   17,12,N'衬衣'----3
   union   all  select   24,4,N'床上用品'---2
   union   all  select   25,24,N'床套'----3
   union   all  select   26,24,N'被单'----3
   union   all  select   27,24,N'枕套'----3
DECLARE @b TABLE(bid int ,Id int,  [mMoney]   int)   
    insert   @b   select   1,8,10
     union   all  select   2,9,30  
      union   all  select   14,19,21
     union   all  select   15,20,10
     union   all  select   3,13,2
     union   all  select   4,14,18
     union   all  select   12,22,21
     union   all  select   13,23,21    union   all  select   5,15,2
    union   all  select   6,16,21
    union   all  select   7,17,8  
     union   all  select   9,25,21
     union   all  select   10,26,8 
     union   all  select   11,27,7 DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @a
WHERE PID=0
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT cast(a.ID as varchar(10)),@Level,b.Sort+','+cast(a.ID as varchar(10))
FROM @a a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
ENDdeclare @lsb table(sort varchar(30),mMoney money)
insert @lsb
select t.sort,b.mMoney 
from @t_level t LEFT JOIN @b b on ','+t.sort+',' like '%,'+cast(b.id as varchar(10))+',%' 
where t.level>1    ----- 如果子节点在两层以上，把条件变成t.level>1
select  f.id,类型=f.name,合计=sum(e.mMoney)
from  @t_level d left join 
  @lsb e on charindex(','+d.sort+',',','+e.sort+',')>0
inner join @a f on d.id=f.id 
where level=0     ----- 想查询哪一层的汇总就变成相应的数值，根节点是0
GROUP BY f.id,f.name
order by f.idselect  f.id,类型=f.name,合计=sum(e.mMoney)
from  @t_level d left join 
  @lsb e on charindex(','+d.sort+',',','+e.sort+',')>0
inner join @a f on d.id=f.id 
where level=1     ----- 想查询哪一层的汇总就变成相应的数值，第一层的汇总
GROUP BY f.id,f.name
order by f.idselect  f.id,类型=f.name,合计=sum(e.mMoney)
from  @t_level d left join 
  @lsb e on charindex(','+d.sort+',',','+e.sort+',')>0
inner join @a f on d.id=f.id 
where level=2     ----- 想查询哪一层的汇总就变成相应的数值，第二层的汇总
GROUP BY f.id,f.name
order by f.id
id          类型         合计                    
----------- ---------- --------------------- 
1           电脑         71.0000
2           房子         62.0000
4           日用品        67.0000(3 row(s) affected)id          类型         合计                    
----------- ---------- --------------------- 
5           显示器        40.0000
11          电视         20.0000
12          洗涤用品       31.0000
18          键盘         31.0000
21          沙发         42.0000
24          床上用品       36.0000(6 row(s) affected)id          类型         合计                    
----------- ---------- --------------------- 
8           显像管        10.0000
9           扇热片        30.0000
13          机顶合        2.0000
14          遥控器        18.0000
15          牙膏         2.0000
16          毛巾         21.0000
17          衬衣         8.0000
19          螺丝         21.0000
20          键扭         10.0000
22          沙发套        21.0000
23          靠枕         21.0000
25          床套         21.0000
26          被单         8.0000
27          枕套         7.0000(14 row(s) affected)

Answer 8

先结束该帖，新帖 
http://community.csdn.net/Expert/topic/5579/5579639.xml?temp=.3854029感谢 leo_lesley(leo)