146.逐级汇总-案例

1.用户自定义函数

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
--*/

2.循环组件累计法

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
--*/