如何在表格中找出累计金额 [英] How to find out cumulative Amount in a table

问题描述

亲爱的，



我需要这样的数据

 C1 C2 C3 C4 
  -   -   -   -  
 
 R1 0 3 0 k 
 
 R2 1 4 k * 4/365 k +（k * 4/365）
 
 R3 2 3（k +（k * 4/365））* 3 [（k +（k * 4/365））* 3] + [k +（k * x / 365） ）] 

价值



k = 4

 C1 C2 C3 C4 
  -   -   -   -  
 
 R1 0 3 0 4 
 
 R2 1 4 0.04 4.04 
 
 R3 2 3 12.13 16.18 

我需要到达C3& C4列基于给定的公式。如何解决这个问题



代码块添加 - OriginalGriff [/ edit]

解决方案

< pre lang =sql> DECLARE @ C1 as int
DECLARE @ C2 as int
DECLARE @ C3 as float
DECLARE @ C4 as float

DECLARE @ LAST_C1 as int
DECLARE @ LAST_C4 as float

SET @ LAST_C1 = NULL

DECLARE cp_run CURSOR
FOR SELECT C1，C2，C3，C4 FROM cp
OPEN cp_run

FETCH NEXT FROM cp_run
INTO @ C1 ， @ C2 ， @ C3 ， @ C4

WHILE @@ FETCH_STATUS = 0
BEGIN
IF （ NOT @ LAST_C1 IS NULL ）
BEGIN
SELECT @ LAST_C4 = C4 FROM CP WHERE C1 = @ LAST_C1

更新 cp SET C3 =（ @ LAST_C4 * @ C2 ）/ < span class =code-digit> 365 WHERE C1 = @ C1
更新 cp SET C4 = @ LAST_C4 +（（ @ LAST_C4 * @ C2 ）/ 365 ） WHERE C1 = @ C1
END

SET @ LAST_C1 = @ C1

FETCH NEXT FROM cp_run
INTO @ C1 ， @ C2 ， @ C3 ， @ C4
END

CLOSE cp_run
DEALLOCATE cp_run

SELECT * FROM cp

Dear All,

I need data like this

   C1  C2  C3              C4
   --  --  --              --

R1 0   3   0               k
  
R2 1   4   k*4/365         k+(k*4/365)

R3 2   3  (k+(k*4/365))*3  [(k+(k*4/365))*3]+[k+(k*x/365)]

In values

k=4

   C1  C2  C3      C4
   --  --  --      --

R1 0   3   0       4
  
R2 1   4   0.04    4.04

R3 2   3   12.13   16.18

I need to arrive C3 & C4 column based on given formula. How to arrieve this

[edit]Code block added - OriginalGriff[/edit]

解决方案

DECLARE @C1 as int
DECLARE @C2 as int
DECLARE @C3 as float
DECLARE @C4 as float

DECLARE @LAST_C1 as int
DECLARE @LAST_C4 as float

SET @LAST_C1 = NULL

DECLARE cp_run CURSOR
    FOR SELECT C1, C2, C3, C4 FROM cp
OPEN cp_run

FETCH NEXT FROM cp_run
INTO @C1, @C2, @C3, @C4

WHILE @@FETCH_STATUS = 0
BEGIN
	IF(NOT @LAST_C1 IS NULL)
	BEGIN
		SELECT @LAST_C4 = C4 FROM CP WHERE C1 = @LAST_C1
		
		UPDATE cp SET C3 = (@LAST_C4 * @C2) / 365 WHERE C1 = @C1
		UPDATE cp SET C4 = @LAST_C4 + ((@LAST_C4 * @C2) / 365) WHERE C1 = @C1
	END
	
	SET @LAST_C1 = @C1
	
	FETCH NEXT FROM cp_run
	INTO @C1, @C2, @C3, @C4
END

CLOSE cp_run
DEALLOCATE cp_run

SELECT * FROM cp

这篇关于如何在表格中找出累计金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！