逐级汇总示例(循环逐级累计法)–邹老大. 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 –*/
逐级汇总示例(用户定义函数法) — 邹老大 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 FUNCTION f_id() RETURNS @t TABLE(ID int,Level int,SID varchar(8000)) AS BEGIN DECLARE @Level int SET @Level=1 Insert @t Select ID,@Level,’,'+CAST(ID as varchar)+’,’ FROM tb Where PID IS NULL WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 Insert @t Select a.ID,@Level,b.SID+CAST(a.ID as varchar)+’,’ FROM tb a,@t b Where a.PID=b.ID AND b.Level=@Level-1 END RETURN END GO
–调用函数实现实现累计 Select a.ID,a.PID,a.Num,SUM_Num=SUM(b.Num) FROM tb a,f_id() a1, tb b,f_id() b1 Where a.ID=a1.ID AND b.ID=b1.ID AND b1.SID LIKE a1.SID+’%’ GROUP BY a.ID,a.PID,a.Num /*–结果 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 –*/
转载于:https://www.cnblogs.com/Gaojier/archive/2010/04/08/2783619.html
