两层循环处理数据库历史数据

----累计对账金额=SUM(本期对账金额)-SUM(本期扣款金额)

declare @HTID decimal,@DJQ decimal,@QC decimal,@NUM INT,@i int,@ID INT;

if 0!=dbo.existstable('#WL') 
begin
drop table #WL;
end;
select ROW_NUMBER() over (order by F_HT_ID desc) AS ID,
F_HT_ID,count(*) AS SL 
INTO [#WL]
from T_PM_HT_ZLZZCLHTGCDZD
group by F_HT_ID;
SET @ID =1;
set @i=0;
while @i<(SELECT MAX(ID) FROM #WL)
BEGIN
	SELECT @HTID=F_HT_ID FROM #WL
	WHERE ID=@ID;
	SET @ID=@ID+1;
	SET @i=@i+1;
	SELECT @QC=COUNT(*) FROM T_PM_HT_ZLZZCLHTGCDZD
	WHERE F_HT_ID=@HTID;
	SELECT @DJQ=MAX(F_DZQC) FROM T_PM_HT_ZLZZCLHTGCDZD
	WHERE F_HT_ID=@HTID;
	SET @NUM = 0;
	WHILE @NUM<@QC
	begin
		if 0!=dbo.existstable('#BQ') 
		begin
		drop table #BQ;
		end;
		select 
		TOP 1
		F_ID,			
		F_DZQC AS DJQ,			--第几期
		ISNULL(F_BQDZJE,0.00)-ABS(ISNULL(F_BQKK,0.00)) AS BQJE	--本期金额
		into [#BQ]	
		from T_PM_HT_ZLZZCLHTGCDZD
		where F_HT_ID=@HTID
		AND F_DZQC=@DJQ
		ORDER BY F_DZQC DESC;
		
		if 0!=dbo.existstable('#WQ') 
		begin
		drop table #WQ;
		end;
		select 
		SUM(ISNULL(F_BQDZJE,0.00))-ABS(SUM(ISNULL(F_BQKK,0.00))) AS WQJE
		INTO [#WQ]
		from T_PM_HT_ZLZZCLHTGCDZD 
		WHERE F_HT_ID=@HTID
		AND F_DZQC < (SELECT DJQ FROM #BQ);
		
		if 0!=dbo.existstable('#ZZ') 
		begin
		drop table #ZZ;
		end;
		SELECT 
		F_ID,
		DJQ,
		BQJE+(SELECT WQJE from #WQ) as zzje
		INTO [#ZZ]
		FROM #BQ;
		
		UPDATE T_PM_HT_ZLZZCLHTGCDZD  
		SET F_LJHTDZJE =ISNULL(ZZ.zzje,0.00)
		FROM T_PM_HT_ZLZZCLHTGCDZD ZLZZCLHTGCDZD
		JOIN #ZZ ZZ ON ZLZZCLHTGCDZD.F_ID=ZZ.F_ID
		WHERE F_HT_ID=@HTID
		AND ZLZZCLHTGCDZD.F_ID=ZZ.F_ID;
		set @NUM=@NUM+1;
		SET @DJQ=@DJQ-1;
	end;
END;