22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE tb(ID int PRIMARY KEY,PID int,Num int)
INSERT tb SELECT 1,NULL,100
UNION ALL SELECT 2,1 ,200
UNION ALL SELECT 3,2 ,300
UNION ALL SELECT 4,3 ,400
UNION ALL SELECT 5,1 ,500
UNION ALL SELECT 6,NULL,600
UNION ALL SELECT 7,NULL,700
UNION ALL SELECT 8,7 ,800
UNION ALL SELECT 9,7 ,900
GO
--计算的存储过程
CREATE PROC p_Calc
AS
SET NOCOUNT ON
DECLARE @Level int
SET @Level=1
SELECT ID,PID,SUM_Num=Num,
Level=CASE
WHEN EXISTS(SELECT * FROM tb WHERE PID=a.ID)
THEN 0 ELSE 1 END
INTO # FROM tb a
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
UPDATE a SET
Level=@Level,
SUM_Num=ISNULL(a.SUM_Num,0)+ISNULL(b.SUM_Num,0)
FROM # a,(
SELECT aa.PID,SUM_Num=SUM(aa.SUM_Num)
FROM # aa,(SELECT DISTINCT PID FROM # WHERE Level=@Level-1)bb
WHERE aa.PID=bb.PID
AND NOT EXISTS(
SELECT * FROM # WHERE PID=aa.PID AND Level=0)
GROUP BY aa.PID
)b WHERE a.ID=b.PID
END
SELECT a.*,b.SUM_Num
FROM tb a,# b
WHERE a.ID=b.ID
GO
--调用存储过程进行计算
EXEC p_Calc
/*--结果
ID PID Num SUM_Num
---------------- ------------------ ------------------- --------------------
1 NULL 100 1500
2 1 200 900
3 2 300 700
4 3 400 400
5 1 500 500
6 NULL 600 600
7 NULL 700 2400
8 7 800 800
9 7 900 900
--*/
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-13 20:37:26
---------------------------------
--> 生成测试数据表:tb
If not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([level] int,[item] nvarchar(4),[cost] int)
Insert tb
Select 0,'AA',null union all
Select 1,'A1',1 union all
Select 1,'A2',1 union all
Select 2,'CC',1 union all
Select 1,'DD',2 union all
Select 0,'KK',null union all
Select 1,'K1',5 union all
Select 1,'K2',5 union all
Select 2,'K21',4 union all
Select 3,'K213',2 union all
Select 1,'K3',1
Go
--Select * from tb
-->SQL查询如下:
alter table tb add id int identity
go
select * into # from tb order by id desc
declare @i int,@j int
update # set
cost=case when cost is not null then cost else @i end,
@i=case when @j is null then 0 else isnull(@i,0)+isnull(@j,0) end,
@j=cost
update tb set
cost=a.cost
from # a
where tb.level=a.level
and tb.item=a.item
and tb.level=0
alter table tb drop column id
go
select * from tb
/*
level item cost
----------- ---- -----------
0 AA 5
1 A1 1
1 A2 1
2 CC 1
1 DD 2
0 KK 17
1 K1 5
1 K2 5
2 K21 4
3 K213 2
1 K3 1
(11 行受影响)
*/
drop table #
如果只是更新结果,参考以上。