to dawugui: 在下面这个贴子中,您提供了f_cid(ID)的求和方法,对单个求和速度正常,但求和的字段很多时,速度较慢,我希望能一次利用这个函数求出多个数值的汇总数据,这样速度应该有所提高,不知道如何实现?谢谢 http://topic.csdn.net/u/20090122/20/6ba43505-561a-4749-928c-d7be5e37cc1c.html
-- 试试这种逐层汇总的方式create table tb(ID int, PID int, NAME varchar(10) , VALUE int, [LEVEL] int ) insert into tb values(0 , null, 'x1' , null, 1) insert into tb values(1 , 0 , 'x2' , null, 2) insert into tb values(2 , 1 , 'x3' , 100 , 3) insert into tb values(3 , 1 , 'x4' , 200 , 3) go--计算的存储过程 create proc p_calc as set nocount on declare @l int set @l=1select [id],[pid],[sumnum]=VALUE ,level=case when exists(select * from [tb] where [pid]=a.[id]) then @l-1 else @l end into [#] from [tb] a if @@rowcount>0 create index IDX_#_id_pid on [#]([id],[pid]) else set @l=999while @@rowcount>0 or @l=1 begin set @l=@l+1 update a set level=@l,[sumnum]=isnull(a.[sumnum],0)+isnull(b.[sumnum],0) from [#] a,( select aa.pid,[sumnum]=sum(aa.[sumnum]) from [#] aa,( select distinct [pid] from [#] where level=@l-1 )bb where aa.[pid]=bb.[pid] AND NOT EXISTS( SELECT * FROM [#] WHERE [PID]=aa.[PID] AND [Level]=0) GROUP BY aa.[PID] having sum(case when aa.level=0 then 1 else 0 end)=0 )b where a.[id]=b.[pid] end select a.*,b.[sumnum] from [tb] a,[#]b where a.[id]=b.[id] go--调用存储过程进行计算 exec p_calc go--删除测试 drop table [tb] drop proc p_calc/*--测试结果 ID PID NAME VALUE LEVEL sumnum ----------- ----------- ---------- ----------- ----------- ----------- 0 NULL x1 NULL 1 300 1 0 x2 NULL 2 300 2 1 x3 100 3 100 3 1 x4 200 3 200(所影响的行数为 4 行) --*/
在下面这个贴子中,您提供了f_cid(ID)的求和方法,对单个求和速度正常,但求和的字段很多时,速度较慢,我希望能一次利用这个函数求出多个数值的汇总数据,这样速度应该有所提高,不知道如何实现?谢谢
http://topic.csdn.net/u/20090122/20/6ba43505-561a-4749-928c-d7be5e37cc1c.html
insert into tb values(0 , null, 'x1' , null, 1)
insert into tb values(1 , 0 , 'x2' , null, 2)
insert into tb values(2 , 1 , 'x3' , 100 , 3)
insert into tb values(3 , 1 , 'x4' , 200 , 3)
go--计算的存储过程
create proc p_calc
as
set nocount on
declare @l int
set @l=1select [id],[pid],[sumnum]=VALUE
,level=case
when exists(select * from [tb] where [pid]=a.[id])
then @l-1 else @l end
into [#] from [tb] a
if @@rowcount>0
create index IDX_#_id_pid on [#]([id],[pid])
else
set @l=999while @@rowcount>0 or @l=1
begin
set @l=@l+1
update a set level=@l,[sumnum]=isnull(a.[sumnum],0)+isnull(b.[sumnum],0)
from [#] a,(
select aa.pid,[sumnum]=sum(aa.[sumnum])
from [#] aa,(
select distinct [pid] from [#]
where level=@l-1
)bb where aa.[pid]=bb.[pid]
AND NOT EXISTS(
SELECT * FROM [#] WHERE [PID]=aa.[PID] AND [Level]=0)
GROUP BY aa.[PID]
having sum(case when aa.level=0 then 1 else 0 end)=0
)b where a.[id]=b.[pid]
end
select a.*,b.[sumnum]
from [tb] a,[#]b
where a.[id]=b.[id]
go--调用存储过程进行计算
exec p_calc
go--删除测试
drop table [tb]
drop proc p_calc/*--测试结果 ID PID NAME VALUE LEVEL sumnum
----------- ----------- ---------- ----------- ----------- -----------
0 NULL x1 NULL 1 300
1 0 x2 NULL 2 300
2 1 x3 100 3 100
3 1 x4 200 3 200(所影响的行数为 4 行)
--*/