请教一个sql语句,关于树型的数据统计问题

主要问题是一个树型的数据统计问题

有一个数据表，结构和数据如下：
pkid topic upNode（父节点,0为根接点）  isLeaf（是否叶节点） quantity（如果是叶节点会有数值，其他接点统计得来）
 1     a     0                          0
 2     b     0                          0
 3     a1    1                          0
 4     a11   3                          1                       10
 5     a12   3                          1                       20
 4     a2    1                          1                       30
 5     b1    2                          0
 6     b11   5                          1                       10
 7     b12   5                          1                       20

希望得到：
                        
 1     a     0                          0                       60
 2     b     0                          0                       30
 3     a1    1                          0                       30
 4     a11   3                          1                       10
 5     a12   3                          1                       20
 4     a2    1                          1                       30
 5     b1    2                          0                       30
 6     b11   5                          1                       10
 7     b12   5                          1                       20

如何写这段查询语句，或者说是怎么实现，利用试图也可以

21 个解决方案

#1

说清楚，要得到结果的规则

#2

--处理函数
create function f_calc(@pkid int)
returns int
as
begin
declare @t table(id int,level int)
declare @l int
set @l=0
insert @t select @pkid,@l
while @@rowcount>0
begin
set @l=@l+1
insert @t select a.pkid,@l
from 表 a join @t b on a.upNode=b.id
where b.level=@l-1
end
select @l=sum(quantity) 
from 表 a join @t b on a.pkid=b.id
return(@l)
end
go

--调用实现查询
select pkid,topic,upNode,isLeaf,quantity=dbo.f_calc(pkid)
from 表

#3

楼主的数据好像有点问题吧?

pkid 不是主键吗? 怎么会有重复?

#4

快喊版主，邹建大侠过来

#5

就是统计，比方说：
a=a1+a2;a1=a1+a12
b=b1;b1=b11+b12

#6

to:zjcxc(: 邹建 :)
不好意思，写错了，pkid是主键
pkid topic upNode（父节点,0为根接点）  isLeaf（是否叶节点） quantity（如果是叶节点会有数值，其他接点统计得来）
 1     a     0                          0
 2     b     0                          0
 3     a1    1                          0
 4     a11   3                          1                       10
 5     a12   3                          1                       20
 6     a2    1                          1                       30
 7     b1    2                          0
 8     b11   7                          1                       10
 9     b12   7                          1                       20

希望得到：
                        
 1     a     0                          0                       60
 2     b     0                          0                       30
 3     a1    1                          0                       30
 4     a11   3                          1                       10
 5     a12   3                          1                       20
 6     a2    1                          1                       30
 7     b1    2                          0                       30
 8     b11   7                          1                       10
 9     b12   7                          1                       20

#7

不用临时表行不行？

#8

create table #a (pkid char(12),topic char(12) ,upnode int ,isleaf int,quantity int )
go
insert into #a select '1','a',0,0,0
UNION all select '2','b',0,0,0
UNION all select '3','a1',1,0,0
UNION all select '4','a11',3,1,10
UNION all select '5','a12',3,1,20
UNION all select '4','a2',1,1,30
UNION all select '5','b1',2,0,0
UNION all select '6','b11',5,1,10
UNION all select '7','b12',5,1,20
go

select a.pkid,a.topic,a.upnode,a.isleaf ,
quantity = (select sum(isnull(quantity,0)) from #a where left(topic,len(a.topic)) = a.topic
)
from #a a
go
drop table #a
go

#9

--测试

--测试数据
create table 表(pkid int,topic varchar(10),upNode int,isLeaf bit,quantity int)
insert 表 select 1,'a',0,0,null
union all select 2,'b',0,0,null
union all select 3,'a1',1,0,null
union all select 4,'a11',3,1,10
union all select 5,'a12',3,1,20
union all select 6,'a2',1,1,30
union all select 7,'b1',2,0,null
union all select 8,'b11',7,1,10
union all select 9,'b12',7,1,20
go

--处理函数
create function f_calc(@pkid int)
returns int
as
begin
declare @t table(id int,level int)
declare @l int
set @l=0
insert @t select @pkid,@l
while @@rowcount>0
begin
set @l=@l+1
insert @t select a.pkid,@l
from 表 a join @t b on a.upNode=b.id
where b.level=@l-1
end
select @l=sum(quantity) 
from 表 a join @t b on a.pkid=b.id
return(@l)
end
go

--调用实现查询
select pkid,topic,upNode,isLeaf,quantity=dbo.f_calc(pkid)
from 表
go

--删除测试
drop table 表
drop function f_calc

/*--测试结果

pkid        topic      upNode      isLeaf quantity    
----------- ---------- ----------- ------ ----------- 
1           a          0           0      60
2           b          0           0      30
3           a1         1           0      30
4           a11        3           1      10
5           a12        3           1      20
6           a2         1           1      30
7           b1         2           0      30
8           b11        7           1      10
9           b12        7           1      20

（所影响的行数为 9 行）
--*/

#10

(9 row(s) affected)

pkid         topic        upnode      isleaf      quantity    
------------ ------------ ----------- ----------- ----------- 
1            a            0           0           60
2            b            0           0           30
3            a1           1           0           30
4            a11          3           1           10
5            a12          3           1           20
4            a2           1           1           30
5            b1           2           0           30
6            b11          5           1           10
7            b12          5           1           20

(9 row(s) affected)

不过速度好象，呵呵，有点问题

#11

因为你的级别不是固定的,所以要么用临时表,那么用自定义函数

#12

可以用自定义函数递归。

#13

to:zjcxc(: 邹建 :) 
你说的级别指的是什么
另外速度慢的话，可不可以做成视图，这样是不是好一点呢，视图里面可以使用函数吗

#14

视图里面当然可以用函数.

我说的级别是指:
id=1,2的为1级
id=3,6,7的为2级,因为它的上级只有一层1或2

#15

--试试,应该这各处理方法的效率会高一些.

--处理函数
create function f_id()
returns @re table(id int,idm varchar(8000),level int)
as
begin
declare @l int
set @l=0
insert @re select pkid,cast(pkid as varchar)+',',@l
from 表 where upNode=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.pkid,b.idm+cast(pkid as varchar)+',',@l
from 表 a join @re b on a.upNode=b.id
where b.level=@l-1
end
return
end
go

--调用实现查询
select a.pkid,a.topic,a.upNode,a.isLeaf,quantity=sum(c.quantity) 
from 表 a 
join dbo.f_id() b on a.pkid=b.id
join(
select b.idm,a.quantity 
from 表 a join dbo.f_id() b on a.pkid=b.id
where a.isLeaf=1 and a.quantity<>0
)c on c.idm like b.idm+'%'
group by a.pkid,a.topic,a.upNode,a.isLeaf

#16

--测试

--测试数据
create table 表(pkid int,topic varchar(10),upNode int,isLeaf bit,quantity int)
insert 表 select 1,'a',0,0,null
union all select 2,'b',0,0,null
union all select 3,'a1',1,0,null
union all select 4,'a11',3,1,10
union all select 5,'a12',3,1,20
union all select 6,'a2',1,1,30
union all select 7,'b1',2,0,null
union all select 8,'b11',7,1,10
union all select 9,'b12',7,1,20
go

--处理函数
create function f_id()
returns @re table(id int,idm varchar(8000),level int)
as
begin
declare @l int
set @l=0
insert @re select pkid,cast(pkid as varchar)+',',@l
from 表 where upNode=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.pkid,b.idm+cast(pkid as varchar)+',',@l
from 表 a join @re b on a.upNode=b.id
where b.level=@l-1
end
return
end
go

--调用实现查询
select a.pkid,a.topic,a.upNode,a.isLeaf,quantity=sum(c.quantity) 
from 表 a 
join dbo.f_id() b on a.pkid=b.id
join(
select b.idm,a.quantity 
from 表 a join dbo.f_id() b on a.pkid=b.id
where a.isLeaf=1 and a.quantity<>0
)c on c.idm like b.idm+'%'
group by a.pkid,a.topic,a.upNode,a.isLeaf
go

--删除测试
drop table 表
drop function f_id

/*--测试结果

pkid        topic      upNode      isLeaf quantity    
----------- ---------- ----------- ------ ----------- 
1           a          0           0      60
2           b          0           0      30
3           a1         1           0      30
4           a11        3           1      10
5           a12        3           1      20
6           a2         1           1      30
7           b1         2           0      30
8           b11        7           1      10
9           b12        7           1      20

（所影响的行数为 9 行）
--*/

#17

这个的效率是不是要高点？
不过有一个缺点，就是嵌套级数不能超过32级。

设你的表名为：table1
函数：
create function Total(@pkid int)
returns int
as
begin
declare @isLeaf int
declare @total int
declare @BranchNote int
/*当前节点为叶节点*/
select @isLeaf=isLeaf, @total=quantity from table1 where pkid=@pkid
if @isLeaf=1 return(@total)
/*当前节点为枝节点*/
select @total=sum(quantity) from table1 where upNode=@pkid

select @BranchNote=count(*) from table1 where upNode=@pkid and isLeaf=0
/*当前节点下的节点没有枝节点*/
if @BranchNote=0 return(@total)

/*当前节点下的节点有枝节点*/
if @total is null set @total=0
select @total=@total+dbo.Total(pkid) from  table1 where upNode=@pkid and isLeaf=0
return(@total)

end
go

调用：
select pkid,topic,upNode,isleaf,dbo.Total(pkid) as quantity from table1

#18

这个的好处是不用临时表，而且仅对当前涉及到的层级进行递归统计。
坏处是最多只能到32层。这是SQL自身限制的:MS SQL SERVER函数最多只能嵌套32层。

#19

测试结果如下：
pkid        topic      upNode      isleaf      quantity    
----------- ---------- ----------- ----------- ----------- 
1           a          0           0           60
2           b          0           0           30
3           a1         1           0           30
4           a11        3           1           10
5           a12        3           1           20
6           a2         1           1           30
7           b1         2           0           30
8           b11        7           1           10
9           b12        7           1           20

（所影响的行数为 9 行）

#20

逛UP

#21

多谢大家，学到了很多